MySQL Join on LIKE statement

Question

I need to count how many users are in each group in a database. Unfortunately the database design is not great and the users uids are stored against the group in the group table in a LONGTEXT field column name owncloudusers. Example of owncloudusers data :

{i:0;s:36:"25C967BD-AF78-4671-88DC-FAD935FF1B26";i:1;s:36:"40D6866B-EA06-4F39-B509-8CE551CC1924";i:2;s:36:"7724C600-DE23-45C8-8BFD-326B0138E029";i:3;s:36:"D6FF37EC-11F4-471F-94C9-F3A28416CF1F";i:4;s:36:"F70C6D03-B7BA-44E4-B703-9AF3EED9BC03";}

I thought I could use a query with a LIKE on the join to compare the user's uid and look inside owncloudusers and see if there is a match.

The closest I have got is:

SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
FROM oc_ldap_group_members T1
LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE('%:"'||T2.owncloud_name||'";%') 
GROUP BY owncloudname;

T1 table holds the groupings and who is tagged to that group T2 table holds the users data. column owncloud_name is the users uid column

I have tried a few approaches I found on stackoverflow CONCAT on the LIKE join and LIKE('%:"'+T2.owncloud_name+'";%')

But no joy. The current statement I have returns 0 users against all the groups but I know this is not right.

I know it much but an issue on the join not sure where to go with it next.

Any assistance would be much appreciated.

Thanks for the reply I have tried the above and it runs without error however, same issue the count returns 0 — N_E, May 17 '16 at 08:10

ScaisEdge · Accepted Answer · 2016-05-17T08:07:08.890

2

I think you need a simple

    SELECT T1.owncloudname, count(*) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers LIKE '%T2.owncloud_name%' 
    GROUP BY owncloudname;

If you need concat try

    SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
    FROM oc_ldap_group_members T1
    LEFT JOIN oc_ldap_user_mapping T2 ON T1.owncloudusers 
       LIKE concat( '%',T2.owncloud_name,'%' )
    GROUP BY owncloudname;

edited May 17 '16 at 08:07

answered May 17 '16 at 07:37

ScaisEdge

131,976
10
91
107

Hi and thanks for the reply unfortunately if you do the above wouldn't it simply try to read T2.owncloud_name as a the string and not the uid i want? – N_E May 17 '16 at 08:04
Thanks scaisEdge your a legend I was really close with some of the approaches I tried I missed the ,, in the concat doh. – N_E May 17 '16 at 08:12

Bohemian · Answer 2 · 2016-05-17T08:09:50.690

1

You were close, but mysql doesn't understand || as a text concatenation operator; use CONCAT() with the text parts passed as a list of values to build the LIKE operand:

SELECT T1.owncloudname, count(T2.owncloud_name) AS Users
FROM oc_ldap_group_members T1
LEFT JOIN oc_ldap_user_mapping T2
    ON T1.owncloudusers LIKE CONCAT('%;', T2.owncloud_name, ';%')
GROUP BY owncloudname;

edited May 17 '16 at 08:09

answered May 17 '16 at 07:41

Bohemian

412,405
93
575
722

Hi thanks for the reply when I try the above it gives me the following error : Operand should contain 1 column(s). any ideas? – N_E May 17 '16 at 08:01
@N_E Ooops... left out `CONCAT()`. Try it now. – Bohemian May 17 '16 at 08:10

score 0 · Answer 3 · answered May 17 '16 at 07:53

if there aint any performance issue, could you try it with sub-query,

    SELECT 
            T1.owncloudname, 
            (SELECT COUNT(*) 
             FROM   oc_ldap_user_mapping AS T2
             WHERE  LOCATE(T2.owncloud_name,T1.owncloudusers)=1) AS Users 
    FROM 
            oc_ldap_group_members T1
    GROUP BY 
            owncloudname;

MySQL Join on LIKE statement

3 Answers3

Linked