grid/smartgrid searching foreign key tables

[Cliff Kachinske]

I define a table so:
db.define_table ('mytable', blah..., format='% (name) s')

So as we know, grid will display the contents of the name field when mytable is used as a foreign table.

But if you try to search on that field, the search only works if you submit record id integers as search parameters.

Since human users don't generally know the record id or ids, this makes the search function less useful than it could be.

Has anyone worked around this? If so, can you share your solution?

Thanks, Cliff Kachinske

[Niphlod]

it's a problem on how to make it work without being heavily limited performance-wise.
Having a custom format almost kills the possibility to fetch those records using an "automatic" join with the referenced table.

It's one thing to fetch for every page the representation of the record for 10 rows, it's a totally different thing have to search through potentially thousands of records with everyone's ultra-custom formatter....

If you need your users to scan the table "ala fulltext", don't use references and formats: prepare a shallow copy table with real-text fields instead of references.

[Cliff Kachinske]

Thanks, Niphlod.  Point taken.

As I think about this, I realize that there's no law requiring me to use the foreign table's primary key as the foreign key reference.

The only advantage in Postgres is the guaranteed uniqueness of the key.  But I should get the same results if I enforce uniqueness on some other field, then I should be able to use it as a reference.  Of course I would have to index it and not allow nulls or empties.

But in my other scenario I would want to index it anyway because it would be a search key, and same for null/empty.

So maybe that's my answer.

[Niphlod]

one point is performances on large tables if your "original" needs some kind of access outside the grid. That's why I recommended a shallow copy to use only in the grid but hey, every app has its own logics.

[Jim S]

I work around this by always coding custom filters for each grid I create.  It is great having the default capabilities that the search_widget provides within the grid, but I feel that it is too complicated for our typical users.  For foreign key fields I will usually provide a SELECT control that will allow them to select the one they want to search on (providing the list isn't too long).  If I want to do an auto-complete I'll use Kenji's suggest_widget.  Not nearly as flexible as the default search_widget but must simpler for the user and also limits them to just the filter controls they need.

-Jim

[Niphlod]

I never had to explain "anything" to the users.... a normal "fulltext" search is provided anyway if they don't dwell with the dropdowns etc etc etc.

[Cliff Kachinske]

Just so I'm clear, full text is when you just type the search term in the search field and click search.

I've been doing the same as Jim and it takes as long as the whole rest of the controller put together.

[Jim Steil]

I agree with Cliff.  It takes longer to code the search/filter than it does the controller with .grid and .smartgrid.  But, I think it is worth the time so I do it.

-Jim

On Mon, Mar 11, 2013 at 8:18 PM, Cliff Kachinske <cjk...@gmail.com> wrote:

Just so I'm clear,  full text is when you just type the search term in the search field and click search.

I've been doing the same as Jim and it takes as long as the whole rest of the controller put together.

--

---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/hrn6VL_g00w/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Niphlod]

I'm starting to miss some bits .... ok for autocomplete, select boxes, filter based on one column only .... but I think the basic functionality of the search widget seems "ok": if users are in a hurry they put something in and all text-ty fields gets searched, if they need an advanced search they can play with the included dropdowns (and they can search for multiple elements too, e.g., inserting orange,watermelon in the value for a column, the query will search for all rows with column wither orange or watermelon).

What else is missing / do you need to code / etc etc ? If it's a generalizable piece of code it can be either shipped as a plugin or included in trunk.
<shallow sponsor mode on>If all the bits you code remain locked in your app, web2py will never grow.</shallow sponsor mode off>


PS: I remember that some user wanted to put up a nice doc for users (not developers) facing the grid/smartgrid .... never heard of him back :(

[Philip Kilner]

Hi Niphlod,

On 12/03/13 08:26, Niphlod wrote:
> PS: I remember that some user wanted to put up a nice doc for users (not
> developers) facing the grid/smartgrid .... never heard of him back :(
>

That was me!

:-)

I've done some simple notes with screen shots for my client, but need to
ask people who know the underlying mechanism better than I if I've made
any errors.

They are currently embedded in a private site in a wiki page, so will
need to strip the confidential bits out and put them somewhere I can
send you a link to.

Realistically, that's more likely to be tomorrow than today, so please
bear with me a little longer...

;-)


--

Regards,

PhilK


'a bell is a cup...until it is struck'

[Niphlod]

no probl. maybe we can embed some "help page" directly too. let me know when it'll be ready.

[Cliff Kachinske]

No, I'm the one who was missing bits :(

The search widget has come a loooooooong way since the last time I experimented with grid.

Thanks to all who have worked on it.

[Alex Glaros]

what is a "shallow copy table"?

Alex Glaros

[Niphlod]

it's a table that is "close to the original", in this case with all columns filled directly with the representation of the eventual foreign key of the original one.

[Alex Glaros]

like de-normalized?

[Niphlod]

yep, in a "programmatic" way cause format can be any callable in the world of the developer mind, but basically the idea is the same.