CakePHP3 filtering based on custom rules

Question

I have following schema:

User hasMany RegistrationState RegistrationState belongsTo User

to its classic 1->M relation where user_id is in RegistrationState table.

RegistrationState has following attributes

id, user_id, state, created_at ... ( others are not relevant )

User can have more registration states for example:

Registration state -> 1, created -> 01-02-2017
Registration state -> 2, created -> 01-03-2017
Registration state -> 3, created -> 01-04-2017
Registration state -> 4, created -> 01-05-2017
Registration state -> 2, created -> 01-06-2017
Registration state -> 3, created -> 01-07-2017

I need to filter based on this 'state property', where the valid state is last one created.

Valid state for this example is 3, because its latest state.

Now, i have controler, and index method where i have

    // push custom query for filtering to finder method
    $this->paginate['finder'] = [
        'clients' => ['q' => $this->request->query]
    ];

    try {
        $clients = $this->paginate( $this->Users );
    } catch (\Exception $e) {
        // redirect on main
        return $this->redirect($this->here);
    }

My finder method looks like this

public function findClients($query, array $options)
{
    $opts = $query->getOptions()['q'];

    if (!empty($opts['email'])) {
        $query->andWhere(['email LIKE' => '%' . trim($opts['email']) . '%']);
    }

    if (!empty($opts['identity_num'])) {
        $cn = $opts['identity_num'];
        $query->matching('Identities', function ($query) use ($cn) {
            $query->andWhere(['Identities.number' => $cn]);
        });
    }

    // registration state
    if (!empty($opts['reg_state'])) {
        // WHAT SHOULD BE HERE???
    }

    return $query;
}

There is also another 1->M relation User -> Identity, but ( matching method ) but that works fine because number is always unique.

I can't resolve problem with registration states, how can I implement this kind of search in CakePHP3? ( i dont want to break pagination so i would like to solve this in finder method )

You want to pass a state to the query and filter users based on whether the latest associated state (in contrast to any associated state) matches the supplied value? That would sound like a [tag:greatest-n-per-group] problem, similar to **[this](http://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group)**, but with an extra condition. No time for a detailed answer right now, sorry. — ndm, Feb 25 '17 at 19:43
@ndm Thanks for your reply. You right and [this](http://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group) is extremely usefull, but i cant implement that because i would broke existing code :/ I've solved it with subquery — xrep, Feb 25 '17 at 21:42

score 0 · Accepted Answer · answered Feb 25 '17 at 21:45

One of possible (dirty?) solutions to this is subquery ( INNER JOIN on RegistrationState )

    // registration state
    if (!empty($opts['reg_state'])) {

        $conn = ConnectionManager::get('default');


        $subquery = $conn->execute(
                'SELECT uir.user_id ' .
                'FROM registration_state uir ' .
                'INNER JOIN ( ' . 
                    'SELECT user_id, max(created) as m_created ' .
                    'FROM registration_state ' .
                    'GROUP BY user_id ' .
                ') muir ON uir.user_id = muir.user_id AND uir.created = muir.m_created AND uir.status = '
                . intval( $opts['reg_state'])
        );

        $usersInStateIds = [];
        // transform result of subquery to usable array
        foreach( $subquery->fetchAll('assoc') as $r ) {
            $usersInStateIds[] = intval( $r['user_id'] );
        }
        $query->matching('RegistrationState', function ($query) use ($usersInStateIds) {
            return $query->andWhere(['RegistrationState.user_id IN' => $usersInStateIds]);
        });
    }

CakePHP3 filtering based on custom rules

1 Answers1