How to create a where clause targeting associated tables

Need Help
1

Hey,

I am working on a project where I want to create a where clause focused on an associations table. The problem is in the following code:

> private function _getLikeArray($columns, $query) {
>         $whereColumnQuery = [];
>
>         foreach ($columns as $key => $value) {
>             $leftHand = $value . ' LIKE';
>             array_push($whereColumnQuery, [$leftHand => '%' . $query . '%']);
>         }
>
>         return $whereColumnQuery;
>     }
>
> $columns = ['unique_identifier', 'birth_date', 'social_security_number', 'Users.last_name', 'Users.UserLocations.Locations.name' ]
> 
>
> $likeArray = $this->_getLikeArray($columns, $query)
> $full_name = $query->func()->concat([
>                       'Users.first_name' => 'identifier', ' ',
>                       'Users.insertion' => 'identifier', ' ',
>                       'Users.last_name' => 'identifier'
>                     ]);
> 
> $query->where(function($exp, $q) use ($textQuery, $full_name, $likeArray) {
>                            return $exp->or_($likeArray)->like($full_name, '%' . $textQuery . '%');
>                         });

The query above has been performed on a table with the following contains:

'contains' => [
                    'Users' => function ($q) use ($findType) {
                        return $q->find($findType);
                    },
                    'Users.UserLocations.Locations',
                    'Users.UserDepartments.LocationDepartments'
                ]

Along with the (probably redundant) leftJoinWith:
$query->leftJoinWith('Users.UserLocations.Locations');

As you can see, I am dynamically creating an array containing columns which I would like to filter on by the same LIKE condition. All goes right except for the ‘Users.UserLocations.Locations.name’ scenario. This has probably something to do with the fact that the association nesting is rather deep. However, as far as I am concerned this should not be a problem.

Nevertheless, the following error is shown:

Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.name like ‘%Loca%’ OR (CONCAT(Users.first_name, ’ ', Users.insertion, ’ ‘, User’ at line 1

Anyone any ideas?

Thank you for your help!

2

What does your $columns array look like, and what’s the complete SQL generated by this code?

3

The $columns array is specified as:

$columns = ['unique_identifier', 'birth_date', 'social_security_number', 'Users.last_name', 'Users.UserLocations.Locations.name' ]

This gets converted to a set of where clauses with the pattern:
$column . ' LIKE' => $userQuery

In this example giving the final array of:

$likeArray = [ 'unique_identifier LIKE' => '%Loca%', 'birth_date LIKE' => '%Loca%', 'social_security_number LIKE' => '%Loca%', 'Users.last_name LIKE' => '%Loca%', 'Users.UserLocations.Locations.name LIKE' => '%Loca%', ]

the SQL generated by the code is:

SELECT (all_patient_columns_here), (all_user_columns_here), (all_user_location_linking_table_columns_here) 

FROM patients Patients 
LEFT JOIN users Users ON (Users.id = (Patients.user_id) AND (Users.deleted_at) IS NULL) 
LEFT JOIN user_locations UserLocations ON Users.id = (UserLocations.user_id) 
LEFT JOIN locations Locations ON Locations.id = (UserLocations.location_id) 

WHERE 
(unique_identifier like :c0 OR 
birth_date like :c1 OR 
social_security_number like :c2 OR 
Users.last_name like :c3 OR
Users.UserLocations.Locations.name like :c4 OR 
(CONCAT(Users.first_name, :param5, Users.insertion, :param6, Users.last_name)) LIKE :c7)

ORDER BY Users.last_name ASC

We can see that indeed the location columns are missing, but I do not understand why. Do I need to add manual select statements?

4

Users.UserLocations.Locations.name like :c4 does not look like valid SQL. I expect that’s the source of your error.

Probably require some change for the location columns too, but that’s a separate thing from the error.

1 Like
5

Why is that invalid? Is it invalid naming or invalid referencing? In the select statement none of the Location columns are selected, isn’t that the main problem here?

6

Users.UserLocations.Locations.name is simply not a valid SQL identifier. It needs to just be Locations.name. Having none of the Location columns selected means that you simply won’t get data for them; the bad identifier is what’s causing the syntax error.

1 Like
7

Thanks, great! That is working. What did you mean by the following?

8

I simply meant that the syntax error reported by MySQL was unrelated to the columns being selected, but that once that error is fixed, you’re likely to see that the data you get doesn’t have everything you want, and you’ll need to make a further change to correct that.

1 Like