Thread: Join several tables (to fetch user info), but one of them is optional (user avatar)

Hello,

given a user name in a Drupal 7.17 database
using PostgreSQL 8.4.13 I am trying to fetch user info
(uid, city, gender, avatar) distributed over several tables.

The avatar is however optional - some users don't have it.

For users, that do have avatars my query works well:

#  select
        u.uid,
        /* u.pass, */
        f.filename,
        g.field_gender_value,
        c.field_city_value
from
        drupal_users u,
        drupal_file_managed f,
        drupal_field_data_field_gender g,
        drupal_field_data_field_city c
where
        u.name='Alex' and
        u.picture=f.fid and
        g.entity_id=u.uid and
        c.entity_id=u.uid
;
 uid |         filename         | field_gender_value | field_city_value
-----+--------------------------+--------------------+------------------
   1 | picture-1-1312223092.jpg | Male               | Bochum
(1 row)

However for users, who don't have avatar I get empty result.

When I omit the drupal_file_managed  table - it works again:

#  select
        u.uid,
        /* u.pass, */

        g.field_gender_value,
        c.field_city_value
from
        drupal_users u,

        drupal_field_data_field_gender g,
        drupal_field_data_field_city c
where
        u.name='mvp' and

        g.entity_id=u.uid and
        c.entity_id=u.uid
;
  uid  | field_gender_value | field_city_value
-------+--------------------+------------------
 18539 | Male               | Moscow
(1 row)

How could I modify my join statement to
ensure that it always returns 1 row for valid users -
regardless if they have avatar or not?

Do I want a "left outer join" here?
(I'm afraid it will return several rows instead of 1).

Regards
Alex

Alexander Farber wrote:
> given a user name in a Drupal 7.17 database
> using PostgreSQL 8.4.13 I am trying to fetch user info
> (uid, city, gender, avatar) distributed over several tables.
>
> The avatar is however optional - some users don't have it.
>
> For users, that do have avatars my query works well:
>
> #  select
>         u.uid,
>         /* u.pass, */
>         f.filename,
>         g.field_gender_value,
>         c.field_city_value
> from
>         drupal_users u,
>         drupal_file_managed f,
>         drupal_field_data_field_gender g,
>         drupal_field_data_field_city c
> where
>         u.name='Alex' and
>         u.picture=f.fid and
>         g.entity_id=u.uid and
>         c.entity_id=u.uid
> ;
>  uid |         filename         | field_gender_value | field_city_value
> -----+--------------------------+--------------------+------------------
>    1 | picture-1-1312223092.jpg | Male               | Bochum
> (1 row)
>
> However for users, who don't have avatar I get empty result.
>
> When I omit the drupal_file_managed  table - it works again:
>
> #  select
>         u.uid,
>         /* u.pass, */
>
>         g.field_gender_value,
>         c.field_city_value
> from
>         drupal_users u,
>
>         drupal_field_data_field_gender g,
>         drupal_field_data_field_city c
> where
>         u.name='mvp' and
>
>         g.entity_id=u.uid and
>         c.entity_id=u.uid
> ;
>   uid  | field_gender_value | field_city_value
> -------+--------------------+------------------
>  18539 | Male               | Moscow
> (1 row)
>
> How could I modify my join statement to
> ensure that it always returns 1 row for valid users -
> regardless if they have avatar or not?
>
> Do I want a "left outer join" here?
> (I'm afraid it will return several rows instead of 1).

Yes, you need an outer join for that.

Only use an outer join to add the "drupal_file_managed" table,
the other tables should be joined with an inner join.

Yours,
Laurenz Albe

Thank you, I've ended up with:

# select
        u.uid,
        /* u.pass, */
        f.filename as avatar,
        (g.field_gender_value='Female') as female,
        c.field_city_value as city
from
        drupal_users u LEFT OUTER JOIN drupal_file_managed f on
(u.picture=f.fid),
        drupal_field_data_field_gender g,
        drupal_field_data_field_city c
where
        u.name='mvp' and
        g.entity_id=u.uid and
        c.entity_id=u.uid
;
  uid  | avatar | female |     city
-------+--------+--------+--------------
 18539 |        | f      | Moscow
(1 row)

I wonder though what is the syntax if I wanted
the other 2 tables (drupal_field_data_field_gender
and drupal_field_data_field_city) to be joined
as a "left outer join" as well?

Regards
Alex

On 12/19/2012 5:54 AM, Alexander Farber wrote:
> I wonder though what is the syntax if I wanted
> the other 2 tables (drupal_field_data_field_gender
> and drupal_field_data_field_city) to be joined
> as a "left outer join" as well?

you were using an implied join instead of an explicit one.

     select
         u.uid,
         /* u.pass, */
         f.filename as avatar,
         (g.field_gender_value='Female') as female,
         c.field_city_value as city
    from
           drupal_users u
             JOIN drupal_field_data_field_gender g ON g.entity_id=u.uid
             JOIN drupal_field_data_field_city c ON c.entity_id=u.uid
        LEFT JOIN drupal_file_managed f ON u.picture=f.fid
         where
           u.name='mvp';


is more like how you code a conventional JOIN.