CODEWITHSUNDEEP
phpmysqllaravellaravel-query-builder
tyro
tyro

Reputation: 1428

GROUP_CONCAT gives incorrect values

I have 3 tables: Users, Properties, Units
User table:

user_id   user_name

Properties table

pty_id   pty_name   user   pty_status

Units Table

unit_id   unit_name  pty_id  unit_status

i want to show user details , number of properties and units and their details. Here is my query:

   DB::statement('SET SESSION group_concat_max_len = 10485760');

      $ar = DB::table('users as u')
         ->leftjoin('properties as p', function($join)  {
                   $join->on('p.user_id', '=', 'u.user_id')->where('p.pty_status', '!=' ,0 ); 
                  })

         ->leftJoin(
                 DB::raw("
                  (select COALESCE(count(unit_id),0) AS cntunits, pty_id as temp_pty
                  from  property_units as pu3 
                  left join properties as p2 on pu3.unit_pty_id = p2.pty_id
                  where pu3.unit_status!=0  
                  group by p2.pty_id) as temp"), 'p.pty_id', '=', 'temp.temp_pty')

          ->select(
      DB::raw("group_concat(DISTINCT CONCAT(ej_p.pty_id,'|',ej_p.pty_name,'|',cntunits)) as pty_details"), 
      DB::raw("group_concat(DISTINCT CONCAT(ej_p.pty_id,'|',ej_p.pty_name)) as pty_details_copy")
    )->paginate(10);

When I group_concat the unit_count, only those properties and units are concated where unit exists.

For example the above query returns the following result:

 pty_details              pty_details_copy
  7|I2|2       7|I2, 22|tR ,51|SG APARTMENT,54_||_GA APARTMENTS,

Why properties with units (where unit count=0) are not binding? Where have I gone wrong?

EDIT

Here is the raw query: 

select group_concat(DISTINCT CONCAT(p.pty_id,'|',p.pty_name,'|',cntunits)) as pty_details, 
     group_concat(DISTINCT CONCAT(p.pty_id,'|',p.pty_name)) as pty_details_copy
     from users as  u
     left join properties as p on p.user_id = u.user_id and p.pty_status !=0
     left join 
     (select COALESCE(count(unit_id),0) AS cntunits, pty_id as temp_pty
                  from  property_units as pu3 
                  left join properties as p2 on pu3.unit_pty_id = p2.pty_id
                  where pu3.unit_status!=0  
                  group by p2.pty_id) as temp on p.pty_id = temp.temp_pty

Upvotes: 1

Views: 256

Answers (2)

tyro
tyro

Reputation: 1428

As Solarflare suggested, i got the required result when I changed the query like this:

  $ar = DB::table('users as u')
     ->leftjoin('properties as p', function($join)  {
               $join->on('p.user_id', '=', 'u.user_id')->where('p.pty_status', '!=' ,0 ); 
              })

     ->leftJoin(
             DB::raw("
              (select COALESCE(count(unit_id),0) AS cntunits, pty_id as temp_pty
              from  property_units as pu3 
              left join properties as p2 on pu3.unit_pty_id = p2.pty_id
              where pu3.unit_status!=0  
              group by p2.pty_id) as temp"), 'p.pty_id', '=', 'temp.temp_pty')

      ->select(
  DB::raw("group_concat(DISTINCT CONCAT(ej_p.pty_id,'|',ej_p.pty_name,'|',coalesce(cntunits, 0))) as pty_details"))->paginate(10);

Upvotes: 1

Gerard van Helden
Gerard van Helden

Reputation: 1602

The WHERE from your first join should be part of the ON.

Upvotes: 0

Related Questions