recordfinder query extend

[SledgehammerPL]

I've got big fundamental problem.

My model is a little bit complex:

Person:
id| birth_date

Person_name
id|person_id|first_name|last_name|valid_from|valid_to

And of course Person Model has hasMany set,

 public $hasMany = [
      'person_names' => ['Party\Structure\Models\PersonName','key' => 'person_id'],

and PersonName Model:

public $belongsTo = [
'person' => ['Party\Structure\Models\Person']
];

And now - I set models/person/columns.yaml:

columns:
    first_name:
        label: First Name
        relation: person_names
        select: first_name

    last_name:
        label: Last name
        relation: person_names
        select: last_name

    birth_date:
        label: data urodzenia
        type: text

But showing the columns takes 70 seconds .... The same if I set it in record finder. And I don't know how to set filter, because this below, doesn't work

 $filter->addScopes([
        'test' => [
          'label' => 'Valid on',
          'type' => 'date',
          'conditions' => "(party_structure_person_names.id IS NULL OR party_structure_person_names.id IN (SELECT id FROM party_structure_person_names WHERE ':filtered' BETWEEN valid_from AND valid_to))", 
          'default' => Carbon::now()
        ]
      ]);

If I use (in Controller)

public function ListExtendQuery($query) 
    {
      $personTableName = (new Person)->getTable();
      $personNameTableName = (new PersonName)->getTable();

      $query->addSelect($personNameTableName.'.last_name as last_name');
      $query->addSelect($personNameTableName.'.first_name as first_name');
      $query->leftJoin($personNameTableName, $personTableName.'.id','=',$personNameTableName.'.person_id');
    }

and other columns.yaml:

columns:
    first_name:
        label: First Name
        type: text
    last_name:
        label: Last name
        type: text
    birth_date:
        label: data urodzenia
        type: text
defaultSort:
    column: last_name
    direction: asc

Columns appears in 3 seconds and filter works perfectly. BUT. Record Finder doesn't work at all...

What can I do? I need record finder, but not for the price of 70 seconds for list and every changing sorting or searching

this is sql which takes 73,379.131 ms.
``select "party_structure_people".*, (select string_agg(first_name::VARCHAR, ', ') from "party_structure_person_names" where "party_structure_people"."id" = "party_structure_person_names"."person_id") as "first_name", (select string_agg(last_name::VARCHAR, ', ') from "party_structure_person_names" where "party_structure_people"."id" = "party_structure_person_names"."person_id") as "last_name" from "party_structure_people" order by "first_name" asc limit 20 offset 0`

And my sql which I works in 97.214 ms:
select "party_structure_people".*, party_structure_person_names.first_name, party_structure_person_names.last_name from "party_structure_people" LEFT JOIN "party_structure_person_names" ON "party_structure_people"."id"= "party_structure_person_names"."person_id" order by "first_name" asc limit 20 offset 0
`

[bennothommo]

Just to first check, have you got indexes added on related columns, like the person_id column? And how many records are in these database tables?

Also, which database server are you using?

[bennothommo]

With the first name and last name columns, are you intending to show all names, or just the latest? Or the one that applied with the given valid dates?

[SledgehammerPL]

the one that applied with the given valid dates or the latest - need both in different places (sometimes I don't need filteringby date)

[bennothommo]

You could give this a try - not sure if it'll work or not, but it's worth a shot:

• Ensure your Person Name model has a reverse relation (belongsTo) to the Person model
• Set the record finder so that useRelation is false and the modelClass is set to your Person Name model.
• Add a scope that groups the records by person_id, so that way there should only be one record per person, but it should still allow you to use conditions like the "Valid to" filter.
• Set the keyFrom to be person_id - I assume you are essentially trying to link to a person through the record finder.

This way, you're doing the relation in reverse, but at the end, you should have the person ID which is what you really want out of the record finder.

[SledgehammerPL]

It works! Fast and efficient! Even don't need to use keyFrom :D

Last thing - I'm not sure where and how to add scope to record finder (technically in wintercms, how to prepare query itself is easy)

[bennothommo]

Scopes are just methods in the model that get passed a query builder, and allow you to set conditions or apply logic to the database query. This docs page covers them: https://wintercms.com/docs/database/model#query-scopes

[SledgehammerPL]

model/person/fields.yaml

(...)
issuer:
     label: author
     type: recordfinder
     list: $/party/structure/models/person/columns.yaml
     useRelation: false
     modelClass: Party\Structure\Models\PersonName
     recordsPerPage: 10
     title: find Record
     prompt: Click the find button
     span: left

can I use the scope in fields.yaml? What about parameter?

[mjauvin]

You can use a scope. What parameter do you want to pass to the scope?
ref. https://wintercms.com/docs/backend/forms#widget-recordfinder

[SledgehammerPL]

I'm opening a controller which has :filtered (a current or selected date). And when I enter the one of elements for edit and call record finder, i want to pass :filtered (the definition of this scope in inital controller is here:

$filter->addScopes([
'test' => [
'label' => 'Valid on',
'type' => 'date',
'conditions' => "(party_structure_person_names.id IS NULL OR party_structure_person_names.id IN (SELECT id FROM party_structure_person_names WHERE ':filtered' BETWEEN valid_from AND valid_to))",
'default' => Carbon::now()
]
]);