findAllByColumn1OrColumn2

Need Help
1

Hi together, I try to implement a search bar for alist of entrys.

I list all users from database but now I want to filter either by email, or by role or by status.

I tried to use the following option for retrieving Data: Dynamic Finders

But when I try to use

$this->Users->findAllByEmailOrRoleOrStatus($email, $role, $status);

and one of the fields is not set, the I get an error.

Expression `Users.email` has invalid `null`

How can I use this dynamically? Some ideas?

Cheers Frank

2

The cleanest approach is using FilterCollection classes and GitHub - FriendsOfCake/search: CakePHP: Easy model searching
This way it is future proof, easy to review/adjust and user-friendly (clean PRG via query strings).

See e.g.

for a live demo implementation.

3

Hi dereuromark,

I downloaded the search plugin and I tried to get it running, reading docu.

But for now it looks like, I can search 1 search element in different fields of a table.

What I wnat to do is searching different values in different fields, eg email = hans@wurst.de and role_id = 1 and status_id = 2

select * from users where email=hans@wurst.de or role_id = 1 or status_id = 2

But I am not sure how to configure the search plugin

4

everything is possible, see docs.
you can always make a custom callback if none of the default ones work.
but it is designed to work with unlimited number of fields and combinations.

5

I do not get it running.

in my searchManager() function in my UserTable I put in there

$this->getBehavior('Search')->searchManager()
        ->value('user_id')
        ->add('email', 'Search.Like', [
            'before' => true,
            'after' => true,
            'fieldMode' => 'OR',
            'comparison' => 'LIKE',
            'wildcardAny' => '*',
            'wildcardOne' => '?',
            'fields' => ['email'],
        ])

This will find user by email. But when I add there a second field like role_id

$this->getBehavior('Search')->searchManager()
        ->value('user_id')
        ->add('email', 'Search.Like', [
            'before' => true,
            'after' => true,
            'fieldMode' => 'OR',
            'comparison' => 'LIKE',
            'wildcardAny' => '*',
            'wildcardOne' => '?',
            'fields' => ['email', 'role_id'],
        ])

Then I get an error because this tries to search the email also in the field role_id, which is a number.

and when I add another field

$this->getBehavior('Search')->searchManager()
        ->value('user_id')
        ->add('email', 'Search.Like', [
            'before' => true,
            'after' => true,
            'fieldMode' => 'OR',
            'comparison' => 'LIKE',
            'wildcardAny' => '*',
            'wildcardOne' => '?',
            'fields' => ['email'],
        ])
        ->add('role_id', 'Search.Like', [
            'before' => false,
            'after' => false,
            'fieldMode' => 'AND',
            'comparison' => 'LIKE',
            'wildcardAny' => '*',
            'wildcardOne' => '?',
            'fields' => ['role'],
        ]);

The role_id will be ignored on the search query.

What I found in docu was that I can use the add() methode several times. But it does not work in my example.

In additional, I do not know, what else can I use instead of like search. I want to find where role_id = 2

Sorry but when I read docu, I have a lot of questions

6

Use what I told you: A proper filter collection class. Using this adhoc add() on the manager itself is not a good idea.

With the class itself you are more flexible in general, and reusable for other actions if needed.

As for your concrete issue:
Why are you not using value() on role? after all, you are doing the same here as for user_id/status_id etc (direct value match).

7

If you REALLY want OR search instead of AND (which is weird, but ok), then you probably need the custom callback here:

  ->add('q', 'Search.Callback', [
      'callback' => function ($query, $args) {
          $value = $args['q'] ?? null;
          if ($value === null) {
              return $query;
          }

          return $query->where([
              'OR' => [
                  'column1' => $value,
                  'column2' => $value,
              ],
          ]);
      },
  ]);

for the same fields.

In your case, with 3 different values, probably more like

      ->add('q', 'Search.Callback', [
          'callback' => function ($query, $args) {
              $or = [];

              if (!empty($args['col1'])) {
                  $or[] = ['column1' => $args['col1']];
              }
              if (!empty($args['col2'])) {
                  $or[] = ['column2' => $args['col2']];
              }
              if (!empty($args['col3'])) {
                  $or[] = ['column3' => $args['col3']];
              }

              return $or ? $query->where(['OR' => $or]) : $query;
          },
      ]);

This yields column1 = val1 OR column2 = val2 OR column3 = val3, each with its own value.

But usually searches are AND combined, so normal value() and like() filters should be totally sufficient.