Level 39
I don't understand your question, give us the input data , the sql you try, the result and the expected result.
Dec 30, 2020
13
Level 4
How to Add Sql query in collection
I want to to add this sql to my DB:: SELECT COUNT(slave_id) FROM salesWHEREslave_id!=0 GROUP BYbill_number`
I tried to add like this but when i run does not return any collection data, because i want to count this column slave_id
$data=DB::table('sales')
->select('users.name as nametag','customers.*','teams.*','locations.*','customer__orders.*','sales.*','devices.*',\DB::raw("group_concat(devices2.Devicenumber SEPARATOR '\n') as slavename"),'sales.created_at as created_at_sale',\DB::raw('(SELECT COUNT(`slave_id`) FROM `sales` WHERE `slave_id` !=0 GROUP BY `bill_number`) as totalslave'))
->leftJoin('customers','customers.id','=','sales.client_id')
->leftJoin('teams','teams.id','=','sales.tag_id')
->leftJoin('locations','locations.id','=','sales.borderName')
->leftJoin('devices','devices.id','=','sales.unit_issue_id')
->leftJoin('users','users.id','=','sales.user_id')
->leftJoin('customer__orders','customer__orders.id','=','sales.TruckNo_id')
->leftJoin("devices as devices2",\DB::raw("FIND_IN_SET(devices2.id,sales.slave_id)"),">",\DB::raw("'0'"))
->where([
['sales.sale_type',2],
['sales.tag_id',$team]
])
->groupBy('sales.bill_number')->get();
Level 1
@emfinanga are you trying to create sub query, I mean query within query?
Level 4
yes @faizali2152
Level 1
@emfinanga have you checked this query using dd():
DB::raw('(SELECT COUNT(`slave_id`) FROM `sales` WHERE `slave_id` !=0 GROUP BY `bill_number'
returned any results separately.
Level 4
this is Works separately
$countSlave=DB::table('sales')->select(DB::raw('COUNT(`slave_id`) as totalslave'))
->where([
['sales.sale_type',2],
['sales.tag_id',$team]
])
->where('slave_id','!=',0)
->groupBy('bill_number')
->get();
Level 1
@emfinanga take a look at this docs too. https://laravel.com/docs/8.x/queries#subquery-joins
Level 4
am using union but am get this error
Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL:
$countSlave=DB::table('sales')->select(DB::raw('COUNT(`slave_id`) as totalslave'))
->where([
['sale_type',2],
['tag_id',$team]
])
->where('slave_id','!=',0)
->groupBy('bill_number');
// dd($t);
$data=DB::table('sales')
->select('users.name as nametag','customers.*','teams.*','locations.*','customer__orders.*','sales.*','devices.*',\DB::raw("group_concat(devices2.Devicenumber SEPARATOR '\n') as slavename"),'sales.created_at as created_at_sale')
->leftJoin('customers','customers.id','=','sales.client_id')
->leftJoin('teams','teams.id','=','sales.tag_id')
->leftJoin('locations','locations.id','=','sales.borderName')
->leftJoin('devices','devices.id','=','sales.unit_issue_id')
->leftJoin('users','users.id','=','sales.user_id')
->leftJoin('customer__orders','customer__orders.id','=','sales.TruckNo_id')
->leftJoin("devices as devices2",\DB::raw("FIND_IN_SET(devices2.id,sales.slave_id)"),">",\DB::raw("'0'"))
->where([
['sales.sale_type',2],
['sales.tag_id',$team]
])
->union($countSlave)
->groupBy('sales.bill_number')
->get();
Level 4
@faizali2152 kindly can you correct me here?
Level 4
Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL:
Unable to union because both query must have same number of columns.
Since you want to combine 2 queries into 1 collection, try to merge together.
Laravel 4.x
$data = DB::table('table')->get();
$data2 = DB::table('table')->get();
$data = Collection::make($data);
$data2 = Collection::make($data2);
$data = $data->merge($data2);
Laravel 5 and above should eloquent query will return collection
$data = DB::table('table')->get();
$data2 = DB::table('table')->get();
$data = $data->merge($data2);
Level 4
If you're trying to add new column to count unique slave, you can try
->addSelect(\DB::raw('COUNT(DISTINCT(slave_id)) as totalslave'))
Level 4
no, i have the column slave_id which has 0 value so i want to count this column but when i right that query it count as 1 while me am expect to count as 0 instead of 1
Level 4
In your query:
(SELECT COUNT(`slave_id`) FROM `sales` WHERE `slave_id` !=0 GROUP BY `bill_number`) as totalslave
You can use query like:
->addSelect(\DB::raw('COUNT(IF(slave_id > 0,1,0) as totalslave'))
Level 4
@spyworld both does not works sir
Please or to participate in this conversation.