1

I want to perform a list of users and extract a list of contacts done by each one, grouped by type of item contacted, in one field. Something like this:

user_id, user_name, user_contact_views
100, john, 'a=21,b=22,c=3'
101, mary, 'a=53'
102, jack, 'b=13,c=5'

I have this query almost working

SELECT
u.id user_id,
u.name user_name,
(select group_concat(type_id_n_contacts)
 from (select CONCAT(item_type_id, '=', count(*)) type_id_n_contacts
        from item_contact ic
        where ic.user_id = u.id
        group by item_type_id) sq_type_id_n_contacts
) as user_contact_views
, ... -- Lots of fields
FROM user u
INNER JOIN -- Other tables, field, where and so on ignored
WHERE ...
ORDER BY ...
LIMIT ...;

But I get

Error Code: 1054. Unknown column 'u.id' in 'where clause'

I cannot use u.id from the nested query and I would appreciate if you guys know any work arround to solve it. What about MySQL variables? How to get the value of u.id to a variable in the subsubquey to use ic.user_id = @user_id?

Important note: I have to use the subquery in the fields section. I can make the query work by creating a temp table in a JOIN with all precalculated users_id related with the count string (see Giulio answer). I know that, however there are too many fields to do it with the whole table of users and make a join unindexed afterwards. That's why I want MySQL perform the subquery only with the user_ids already filtered and limited by the WHERE and LIMIT, in the last phase of the query execution. I hope I explained myself.

Thanks!

Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29

1 Answers1

0

Try to move your subquery in the join part:

    SELECT
    u.id,
    u.name,
     table_tmp.user_cont as user_contact_views,
    , ... -- Lots of fields
    FROM user u
    INNER JOIN
     (select group_concat(type_id_n_contacts) as user_cont, sub_user_id
     from (select CONCAT(item_type_id, '=', count(*)) type_id_n_contacts, ic.user_id as sub_user_id
            from item_contact ic
            group by item_type_id) sq_type_id_n_contacts
    ) as table_tmp ON table_tmp.sub_user_id = u.id
    INNER JOIN -- Other tables, field, where and so on ignored
    WHERE ...
    GROUP BY ...
    ORDER BY ...;
Giulio De Marco
  • 137
  • 6
  • Thanks Giulo, but your query doesn't work. Nevertheless, what you mean is this JOIN: LEFT JOIN (select group_concat(type_id_n_contacts) as user_cont, sub_user_id from (select CONCAT(item_type_id, '=', count(*)) type_id_n_contacts, ic.user_id as sub_user_id from item_contact ic group by item_type_id, user_id) sq_type_id_n_contacts group by sub_user_id) as table_tmp ON table_tmp.sub_user_id = u.id – Emilio Nicolás Jul 04 '14 at 15:28
  • Yes, i mean the join that you pasted in your comment. Obviously i could not try the query, i just wrote an example :) – Giulio De Marco Jul 04 '14 at 15:34
  • It is unreadable, but I get your idea. I can make the query work by createing a temp table with all precalculated users_id related with the count string. I know that, however there are too many fields to do it with the hole table of users and make a join unindexed afterwards. Thats why I want MySQL perform the subquery only with the user_ids already filtered and limited by the WHERE and LIMIT, in the last phase of the query execution. I hope I explained myself. – Emilio Nicolás Jul 04 '14 at 15:35
  • Having MySQL variable whith the value of u.id could make the trick in the subquery with ic.user_id = @user_id, but I cannot make it! :-( – Emilio Nicolás Jul 04 '14 at 15:40
  • Yes, i understand your problem. Try to put a condition "WHERE ic.user_id = u.id " in the sub "SELECT FROM item_contact ic..." of my example. Maybe it can work – Giulio De Marco Jul 04 '14 at 15:42