Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello,
I've been at this for quite some time now and I cannot figure it out and hope I can get some help from someone here. First let me say I am by no means an SQL guru but I try before I ask, problem is I don't understand joins very well and am learning.
Ok now for the issue!
I have a table named 'inventories' with the following fields I need to select:
id,condition_id,stock,year,make.model,trim,vin,slug,created_at
There are other fields in the DB but for this query I only need those.
The other table I am trying to get data from is 'vehicle_stats' with the following fields:
inventory_id,views,quotes,test_drives,credit_apps,friends_referred,payments_calculated,printed,created_at
The relationship is a One to Many, where each inventory can have many vehicle_stats.
Here are the relationships in the models:
Inventory model:
public function vehicleStats()
{
return $this->hasMany('Dbn\Models\Stats\VehicleStat');
}
VehicleStat model:
public function vehicle()
{
return $this->belongsTo('Dbn\Models\Dealer\Inventory');
}
Each vehicle can HAVE many stats for the same month but only one entry per day. Example:
Query to get the inventories:
$this->dlrInventory
->where('dealer_id', Auth::user()->dealer_id)
->where('condition_id', 2)
->get();
Query to get the total stats for the month and year:
VehicleStat::select(DB::raw('SUM(views) as views,
SUM(quotes) as quotes,
SUM(test_drives) as testDrives,
SUM(credit_apps) as creditApps,
SUM(friends_referred) as friendsReferred,
SUM(payments_calculated) as paymentsCalculated,
SUM(printed) as printed, inventory_id'))
->where(DB::raw('MONTH(created_at)'), '=', $month)
->where(DB::raw('YEAR(created_at)'), '=', $year)
->where('inventory_id', $vehicleId)
->groupBy('inventory_id')->first();
If I run the above query using 07 as the month, 2018 as the year, and 16 as inventory_id I would get the following:
$vehicle_stats = array(
array('views' => 20,
'quotes' => 20,
'testDrives' => 18,
'creditApps' => 23,
'friendsReferred' => 20,
'paymentsCalculated' => 19,
'printed' => 6,
'inventory_id' => 16,
),
);
That would be the combination of two rows of data. Which is the desired results. However I have to run that query for EACH inventory item to get the results.
Both queries work correct on their own, and I can loop through each record when showing them to get the desired results for display. But this is way more server intensive than I think it needs to be and I figure there has to be a better more efficient way.
My combination attempt:
$testInventory = DB::table('inventories')
->leftJoin('vehicle_stats', 'inventories.id', '=', 'vehicle_stats.inventory_id')
->select(DB::raw('inventories.id, inventories.year'), DB::raw('SUM(vehicle_stats.views) as views,
SUM(vehicle_stats.quotes) as quotes,
SUM(vehicle_stats.test_drives) as testDrives,
SUM(vehicle_stats.credit_apps) as creditApps,
SUM(vehicle_stats.friends_referred) as friendsReferred,
SUM(vehicle_stats.payments_calculated) as paymentsCalculated,
SUM(vehicle_stats.printed) as printed, vehicle_stats.inventory_id'))
->where('inventories.dealer_id', Auth::user()->dealer_id)
->where('inventories.condition_id', 2)
->where(DB::raw('MONTH(vehicle_stats.created_at)'), '=', '07')
->where(DB::raw('YEAR(vehicle_stats.created_at)'), '=', '2018')
->groupBy('vehicle_stats.inventory_id')->get();
Now if I do the following:
$testInventory = DB::table('inventories')
->leftJoin('vehicle_stats', 'inventories.id', '=', 'vehicle_stats.inventory_id')
->select(DB::raw('inventories.id'), DB::raw('SUM(vehicle_stats.views) as views,
SUM(vehicle_stats.quotes) as quotes,
SUM(vehicle_stats.test_drives) as testDrives,
SUM(vehicle_stats.credit_apps) as creditApps,
SUM(vehicle_stats.friends_referred) as friendsReferred,
SUM(vehicle_stats.payments_calculated) as paymentsCalculated,
SUM(vehicle_stats.printed) as printed, vehicle_stats.inventory_id'))
->where('inventories.dealer_id', Auth::user()->dealer_id)
->where('inventories.condition_id', 2)
->where(DB::raw('MONTH(vehicle_stats.created_at)'), '=', '07')
->where(DB::raw('YEAR(vehicle_stats.created_at)'), '=', '2018')
->groupBy('vehicle_stats.inventory_id')
->groupBy('inventories.id')->get();
I get some of the results, but definitely not all. There should be 349 inventory items. Here is what I get with the above query:
Collection {#1393 ▼
#items: array:9 [▼
0 => {#1330 ▼
+"id": 6
+"views": "4"
+"quotes": "8"
+"testDrives": "10"
+"creditApps": "0"
+"friendsReferred": "6"
+"paymentsCalculated": "9"
+"printed": "2"
+"inventory_id": 6
}
1 => {#1331 ▼
+"id": 10
+"views": "0"
+"quotes": "10"
+"testDrives": "14"
+"creditApps": "0"
+"friendsReferred": "10"
+"paymentsCalculated": "10"
+"printed": "1"
+"inventory_id": 10
}
2 => {#1332 ▶}
3 => {#1333 ▶}
4 => {#1334 ▶}
5 => {#1335 ▶}
6 => {#1336 ▶}
7 => {#1337 ▶}
8 => {#1338 ▶}
]
}
If I add anything to the select method I get a GROUP BY error:
SQLSTATE[42000]: Syntax error or access violation: 1055 'dbnmaindb.inventories.year' isn't in GROUP BY (SQL: select inventories.id, inventories.year, SUM(vehicle_stats.views) as views,\n ◀
SUM(vehicle_stats.quotes) as quotes,\n
SUM(vehicle_stats.test_drives) as testDrives,\n
SUM(vehicle_stats.credit_apps) as creditApps,\n
SUM(vehicle_stats.friends_referred) as friendsReferred,\n
SUM(vehicle_stats.payments_calculated) as paymentsCalculated,\n
SUM(vehicle_stats.printed) as printed, vehicle_stats.inventory_id from `inventories` left join `vehicle_stats` on `inventories`.`id` = `vehicle_stats`.`inventory_id` where `inventories`.`dealer_id` = 1 and `inventories`.`condition_id` = 2 and MONTH(vehicle_stats.created_at) = 07 and YEAR(vehicle_stats.created_at) = 2018 group by `vehicle_stats`.`inventory_id`, `inventories`.`id`)
Excuse the very long post, but I wanted to be as detailed as possible. I hope this makes since and someone can help me out.
Best Regards, Ray
The group by error is because the columns you select does not match the ones you group by.
So this will not work
SELECT col1, col2, col3, sum(col4) FROM table GROUP BY col1, col2
They need to match like this
SELECT col1, col2, col3, sum(col4) FROM table GROUP BY col1, col2, col3
So you need to add ->groupBy(inventories.year) to your query
@Tray2 thanks for the info, however this did not fix the problem. Once I add ALL fields I want to query and add the GROUP BY for all the fields it will still only return 41 of the items in the inventories table.
This should be returning 348 even if there is nothing in the vehicle_stats table it should still provide the information for the inventory item.
So in a nutshell there are 348 records in the inventories table and only 41 of them have vehicle_stats data. But I need the data for all 348 records regardless is there is stats data, and the stats data if there is some.
Hope that makes since.
Regards, Ray
I suggest trying something like this. Eloquent is nice but it gets kinda messy when doing advanced queries.
select table1.id,
table1.stock,
table1.year,
table1.make_model,
sum(table2.views) views,
sum(table2.test_drives) test_drives
from table1
left join table2 on table1.id = table2.inventory_id
group by table1.id, table1.stock, table1.year, table1.make_model;
@Tray2 Thanks for the feedback. Your query appears to be working fine when I run it in HeidiSQL or via command line. However, when I set it up in my controller to test it throws an error.
Here is the way I am running it via HeidiSQL and command line:
select inventories.id, inventories.dealer_id, inventories.stock, inventories.year, inventories.make, sum(vehicle_stats.views) views, sum(vehicle_stats.test_drives) test_drives from inventories left join vehicle_stats on inventories.id = vehicle_stats.inventory_id where inventories.condition_id = 2 and inventories.dealer_id = 1 group by inventories.dealer_id, inventories.id, inventories.stock, inventories.year, inventories.make;
Here is how it is setup in the controller for testing:
DB::select('inventories.id, inventories.dealer_id, inventories.stock,
inventories.year, inventories.make, sum(vehicle_stats.views) views, sum(vehicle_stats.test_drives) test_drives
from inventories left join vehicle_stats on inventories.id = vehicle_stats.inventory_id where inventories.condition_id = 2
and inventories.dealer_id = 1 group by inventories.dealer_id, inventories.id, inventories.stock, inventories.year, inventories.make');
Error I receive when testing via the controller:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'inventories.id, inventories.dealer_id, inventories.stock, inventories.y' at line 1 (SQL: inventories.id, inventories.dealer_id, inventories.stock, inventories.year, inventories.make, sum(vehicle_stats.views) views, sum(vehicle_stats.test_drives) test_drives from inventories left join vehicle_stats on inventories.id = vehicle_stats.inventory_id where inventories.condition_id = 2 and inventories.dealer_id = 1 group by inventories.dealer_id, inventories.id, inventories.stock, inventories.year, inventories.make)
What am I doing wrong in my controller?
Thank you in advance for your help.
Regards, Ray
Test it with DB:raw instead.
You have to add select:
DB::select('select inventories.id ...');
Thank you both for the help. @Tray2 using raw didn't help much but as @staudenmeir stated, adding select into DB::select worked.
Again, thank you both for the help. I spent coutless hours trying to make this work with Eloquent without any success.
Regards, Ray
Please or to participate in this conversation.