PHP Postgres JOIN with LIKE query failing-Collection of common programming errors

2 years ago

admin

2 minutes

I am trying to figure out a query with postgres, and Im not sure its cause I am used to mysql and postgres doesn’t have a LIKE query. Or what my deal is. Either way I know its not working and I have no idea why not. Can someone help me out point me in the right direction? I mean ultimately I am trying to make a Zend Database version of this query but, this is the core query I am working with to try and make before I attempt to use zend db class to build it.

SELECT 
    org.orgid, 
    org.roleid, 
    users.userid, 
    users.email, 
    users.first_name, 
    users.last_name, 
    users.contact_id, 
    users.state, 
    users.ts, 
    users.altemail, 
    users.unboundid, 
    users.blocked 
FROM mapping AS org 
INNER JOIN my_users AS users ON org.userid = users.userid
WHERE (org.orgid = 'generated-id')
AND (org.roleid LIKE 'partner-%');



ERROR:  operator does not exist: roles ~~ unknown
LINE 17: AND (org.roleid LIKE 'partner-%');
                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Change
```
AND (org.roleid LIKE 'partner-%');
```
to
```
AND (org.roleid::text LIKE 'partner-%');
```
Evidently you are working on the textual representation of the roleid. this seems like it should work fine except your data types do not match. By casting to text you ensure that they do.

Note that one important ramification is that indexes on roleid will be useless for such a comparison because they operation is not directly compatible with the type. This shouldn’t be a problem since orgid can still use an index but it is something to think about. If you want to fix that, you may want to use table methods or other functions to break this down for you. You can then index function output.

For example you could use a table method as like so:
```
CREATE FUNCTION is_partner(mapping) returns bool language sql immutable as $$
    SELECT $1.roleid::text LIKE 'partner-%';
$$;
```
You could then index it with:
```
CREATE INDEX mapping_is_partner_idx ON mapping (is_partner(mapping));
```
You could then change that join condition from
```
AND (org.roleid LIKE 'partner-%');
```
to
```
AND org.is_partner;
```
Note in this case, org is necessary and cannot be added implicitly since it changes org.is_oartner to is_partner(org) using class.method notation.

Hope this helps.

Originally posted 2013-11-09 22:48:45.