Search on custom pivot table

[Jeff Bell]

Hi,

I hope that everyone is having a productive and exciting New Year so far.  I'm looking forward to the new developments in Zotonic and taking on the challenges of building  a on my lacking expertise of the framework.

That being said, I'm digging in a bit deeper with extending rsc models.  I've realized that I have been relying too heavily on predicates to accomplish certain tasks that are more suited for custom pivot tables.  So I have a basic understanding of pivot tables now - creating, indexing, updating and also sorting template values on them.

But to query based on them, what I've done is created a model behaviour that also exports custom search functions as well as the model methods.

For instance my pivot table is called pivot_workorder, where I capture additional information that I'd like to query on, such as status, location, due_date, assigned_to etc.  So I've created a model called m_pivotworkorder and created functions like this...

Example #1:

m_find_value(status, #m{value=undefined} = M, _Context) ->

    M#m{value=status};

m_find_value(Status, #m{value=status}, Context) ->

    list_rsc(Status, Context);

....

list_rsc(Status, Context) when is_list(Status) ->

    F = fun() ->

        z_db:assoc_props("select * from pivot_workorder where status = $1", [Status], Context)

    end,

    z_depcache:memo(F, {open_workorders, Status}, ?MAXAGE_WORKORDER, Context).

Example #2:

* Note I had to put this in a sub module,  I couldn't export it in my main site module.

observe_search_query({search_query, {location_workorders, [{location, Location}]}, OffsetLimit}, Context) ->

    m_pivotworkorder:search({location_workorders, [{location, Location}]}, OffsetLimit, Context);

search({location_workorders, [{location, Location}]}, _OfffsetLimit, _Context) ->

    #search_sql{

        select="c.*",

        from="pivot_workorder c",

        where="location = $1",

        args=[Location],

        assoc=true

    }.

This allows me to do the following

    {% with m.pivotworkorder.status["up"] as r %}

       {% for m in r %}

           {{ m }} heehaw <br>

       {% endfor %}

    {% endwith %}

And

    {% with m.search[{location_workorders location="South Campus"}] as result%}

       {% for i in result %}

          {{ m.rsc[i.id].title }} is {{ i.status }}<br>

       {% endfor %}

    {% endwith %}

My questions are

1.  I either method prefferred?

2.  Is this the correct way to interact and query pivot table data?

3.  Is there a way to to use m.search[{query ...}] instead?

4.  For some reason I can't have observe_search_query exported in my main site module.  I have to have it in a submodule.  Still trying to figure that one out.

Sorry for the long example.  Maybe I can turn this into a tutorial worthy of submitting to the Zotonic Cookbook.

Thanks,

Jeff

[Andreas Stenius]

Hi Jeff,

Many thanks for your examples. :-)
You're ahead of me regarding custom pivots; so no further input from
me on that. Sorry. :-/

But, the observe_search_query ought to work from the main site module,
is my initial reaction. Does observing other events work from your
main site module?

I've not used the search_query event, but other events successfully
from the main site module.

Cheers,
Andreas

2012/1/18 Jeff Bell <jeff....@gmail.com>:

[Jeff Bell]

Okay,

After trying it again, I am able to export the observe_search_query function from my main module.  Weird ... not sure what I was doing before.

Anyway, I think all the logic for this should probably probably be in its own module anyway, including the do_custom_pivot stuff.

In addition, I wonder if there is a way to have a pivot table that is a one-to-one for only certain rsc based on category.  With only a few records in the database, having all rsc items represented in the pivot table is not an issue, but a larger dataset could introduce a lot of null records.

I plan on looking into this today.  There very well may already be this functionality and I don't know it yet.

Jeff

[Arjan Scherpenisse]

Hi Jeff,

Pivot tables are only needed if you have custom fields defined in an rsc
that you need to search, sort or filter.

For instance see
http://zotonic.com/documentation/887/searchable-keywords

z_pivot_rsc:define_custom_pivot(?MODULE, [{foo, "text"}], Context),

This creates a pivot table named pivot_?MODULE with one column, "foo" of
type text. You need to fill this table on the observe_custom_pivot/2
notification:

observe_custom_pivot({custom_pivot, Id}, Context) ->
Foo = m_rsc:p(Id, foo, Context),
{?MODULE, [{foo, Foo}]}.

This copies the "foo" property from the rsc into your custom pivot table
whenever you change the rsc. "rebuild all search indexes" in the system
status page also calls this for every rsc in the database.

If you need to have entries in your custom pivot table for only a
certain category, you can return the atom 'none' for ids that do not
match it, like this:

observe_custom_pivot({custom_pivot, Id}, Context) ->
case m_rsc:is_a(Id, Context) of
[text, news] ->
Foo = m_rsc:p(Id, foo, Context),
{?MODULE, [{foo, Foo}]};
_ -> none
end.

This only copies the "foo" column in the pivot table for rsc of type news.

Hope this explains it a bit :-)

Arjan

> <mailto:jeff....@gmail.com>>:

> > {{ m.rsc[i.id <http://i.id>].title }} is {{ i.status }}<br>

[Jeff Bell]

Arjan,

Thanks for the response, especially the part about discerning what resources get added to the pivot table.  That definitely makes sense.

What I haven't found in any email threads or documentation, is how to query or filter on these values.  I see how to sort, but not query or filter.

Any 'best practices' that you can suggest would be much appreciated.

Again, thanks for the response,

Jeff

[Arjan Scherpenisse]

You're right Jeff, there are in the standard m.search[{query ...}] not
so much filtering/sorting options for pivot columns.

Most of the time I just create custom search routines just like you did,
using the rsc table and joining the pivot_?MODULE table that is kept in
sync for me.

search({filter_foo, [{value, Value}]}, _OfffsetLimit, _Context) ->
#search_sql{
select="id",
from="rsc r, pivot_mymodule p",
where="r.id = p.id AND foo = $1",
args=[Value]
}.

Arjan

> <mailto:andreas...@astekk.se

> <mailto:andreas...@astekk.se>>> wrote:
> >
> > Hi Jeff,
> >
> > Many thanks for your examples. :-)
> > You're ahead of me regarding custom pivots; so no further
> input from
> > me on that. Sorry. :-/
> >
> > But, the observe_search_query ought to work from the main site
> module,
> > is my initial reaction. Does observing other events work from your
> > main site module?
> >
> > I've not used the search_query event, but other events
> successfully
> > from the main site module.
> >
> > Cheers,
> > Andreas
> >
> >
> > 2012/1/18 Jeff Bell <jeff....@gmail.com
> <mailto:jeff....@gmail.com>

> > <mailto:jeff....@gmail.com <mailto:jeff....@gmail.com>>>:

> > > {{ m.rsc[i.id <http://i.id> <http://i.id>].title

[Jeff Bell]

Thanks, I'm going to write down my experiences with pivot tables to hopefully help others struggling to understand and implementing them.

Jeff