Cortex, paginated search in relations

[Benque Verkoop op Rekening]

Hi,

i'm using latest F3 with latest Cortex and try to build an generic list page applied to 95% of all my models. This page must list the requested model per line and it's relations in aggregated columns such as subtotal amount, total articles and more.

The best example is a model 'orders', related to has-many 'orderlines' and 'belongs-to-one' building. In the list page i want to show all orders, the buildings name and a collapsable set of orderlines per order. This is done.

Next to this list i want to manipulate the results by sorting and pagination. Per default the results must be paginated. I want to be able to sort on relations and aggregated columns. This is all done.

Since ALL results must always be able to paginate i choose to handle all of this in Cortex->paginate().

Now for the tricky part: how can i keep this generic - works on all normal cortex models - system when i want to search on relation AND paginate the outcome?

So to picture it: i want to be able to search in the order based on the building name. I found some hints that ->has() could be an option but that would mean ->has() and ->paginate() must be chainable as i saw ->has()->find() are.

Currently my search logic is translated into applicable $filters[] to be processed by ->paginate() directly. I would love to leave that in tact.

Any help is appreciated!

[ikkez]

you can simply call has() before you do the paginate.. no problem. also have a look at filter() in case you want to sort, limit or paginate the relations as well.

[BQ VOR]

Thanks Ikkez, at this point i've booked some progress. Now i have the searching pretty much done but one issue. Next to paginating, searching i am also implementing sorting. Researching solutions for this i found a link to an update you made to Cortex which enables sorting on a relation. This update makes Cortex create a JOIN on-the-fly to that relation and create a sorting on the column you want.

I piggybacked on this by creating a (search LIKE) filter for Cortex based on the JOIN'ed tablename (created by Cortex) when sorting on the relation. However, when the relation is not sorted on this JOIN is not created and my hack is obviously not working.

Is the ->has() sufficient to tackle this? Could you maybe give me a few example lines of use?

Op zaterdag 17 september 2022 om 15:41:53 UTC+2 schreef ikkez:

[BQ VOR]

To add some context, this is in steps my service (called by the controller) building the array of order+lines to hand over to the template.

Prereqs

1) every model holds also a boolean per field if it is available for sorting and/or searching on

2) all methods used in the chain 'controller -> service -> repository -> model <- Cortex' after the 'controller' support the $options and $filters params to be used by Cortex

Process

1) call is made to controller->index(), which calls service->index()

2) search or sort is set via $options and $filters to the service->index() as they should

3a) when sorting is detected in $options the service checks with the model if there is a relation for the field it needs to sort on

3b) if no relation then sort created by setting field and direction

3c) if it is a relation then the fieldname is set as '@fieldname.relatedModelName'

4a) when searchables fields available in model and searched term is detected search filter code is built

4b) if yes the searchable fields are listed and per field checked if they are from a relation - a WHEN {target} LIKE ... THEN filter is built (weighted search)

4c) {target} for regular fields is table.field, for 'belongs-to-one' relation (what all this is for) it builds RelationTable__RelationField.relationFieldName (which is picked up by the JOIN mentioned earlier)

4d) added to the order part (which is the end of the query) is a CASE .... ELSE END query part to calculate the weight and use it as primary sorting

5) pass end result of $options and $filter to ->paginate()

6) return the rows and totalrows quantity ready to be used in the template

I hope this clearifies a bit :-)

Op vrijdag 30 september 2022 om 10:54:51 UTC+2 schreef BQ VOR:

[ikkez]

well this sounds like a lot of work went into it and probably gives a bit more detailed results with a weighted search.

Cortex itself has a only a way to sort the relations itself, not the main results by values within the relations.. however sorting tasks can be added with the has and filter options as far as i know..

so something like this

$results = $model->filter('relationA', null, ['order'=>'created_at DESC'])->paginate(0,10, $filter, $options);

will order the relations at $results[0]->relationA

ofc you can combine all these like

$model->filter('relA', ['deleted_at = ?', NULL], ['order'=>'created_at ASC']);

$model->filter('relB',['deleted_at = ?', NULL], ['order'=>'type ASC']);

$model->filter('relC',null, ['order'=>'id DESC']);

$model->has('relA', ['type = ?', $search])

$results = $model->paginate(0,10, ['deleted_at = ?', null], ['order'=>'created_at ASC']);