How to write an xpdo query to fetch all of vehicles make

74 Posts

random_noob Reply #1, 9 years, 9 months ago

Hi guys,

I have a database table that has in almost all of the vehicles ever made from 2016 until 1960s. Thats over 63000 records.

For example: I have fields like

I want to write a query that will return the vehicle make only once and to ignore any other the vehicle make if it is found again.

example
Audi
Abarth
Suzuki
Toyota
etc

Then once I have those names, I want to use them as a filter. Whenever a user selects a vehicle make, I want to search the database table and return all of its trims once

example
Make: Suzuki

Model
Swift
Vitara
Jimny
etc

Here is what I have

$sql = "SELECT model_make_id FROM modx_02_models";
foreach ($modx->query($sql) as $row) {
    $output .= $row['model_make_id'] .'<br/>';
}
return $output;

And it is return everything.

How can I do the above and what the best and most efficient way of doing this? Because if several or 100 of users make these queries often on the website, won't it affect the server's performance?

This question has been answered by BBloke. See the first response.

discuss.answer

315 Posts

BBloke Reply #2, 9 years, 9 months ago

I think GROUP BY model_make_id would do the trick

$sql = "SELECT model_make_id FROM modx_02_models GROUP BY model_make_id;";
foreach ($modx->query($sql) as $row) {
    $output .= $row['model_make_id'] .'<br/>';
}
return $output;

BBloke

24,544 Posts

BobRay Reply #3, 9 years, 9 months ago

Another way to go is to use MIN(model_name) or MAX(model_name) in the query to make sure you only get one from each category rather than GROUP BY. I think it might be faster, but I'm not sure.

https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

74 Posts

random_noob Reply #4, 9 years, 9 months ago

Hi guys,

Thanks for the suggestion and it works.

Answered How to write an xpdo query to fetch all of vehicles make