@empoleon: The number of queries you get will depend on how many records there are in each table. I’ll try to explain which code causes which queries:
This one is run to determine if a corresponding table exists for each of your models:
SHOW TABLES FROM `your_database_name`
These are run once per model to get the list of field names:
SELECT * FROM `jobs` LIMIT 1
SELECT * FROM `companies` LIMIT 1
SELECT * FROM `products` LIMIT 1
This one…
...is executed when you do:
This one…
SELECT `companies`.*
FROM (`companies`)
LEFT JOIN `companies_jobs` ON `companies`.`id` = `companies_jobs`.`company_id`
LEFT JOIN `jobs` ON `jobs`.`id` = `companies_jobs`.`job_id`
WHERE `jobs`.`id` = 1
...is executed when you do:
You’ll get a similar query each time you loop through the 1st foreach, for each job ID, so you might have multiple ones like this.
Note that I don’t think your show_jobs() method ever gets into the 2nd foreach because you’re missing a get() on the $job->product. It should have:
foreach ($job->product->get()->all as $product)
This causes this type of query:
SELECT `products`.*
FROM (`products`)
LEFT JOIN `jobs_products` ON `products`.`id` = `jobs_products`.`product_id`
LEFT JOIN `jobs` ON `jobs`.`id` = `jobs_products`.`job_id`
WHERE `jobs`.`id` = 1
Again, you’ll get a similar query each time you loop through the 1st foreach, for each job ID, so you might have multiple ones like this.
This one…
SELECT `companies`.*
FROM (`companies`)
LEFT JOIN `companies_products` ON `companies`.`id` = `companies_products`.`company_id`
LEFT JOIN `products` ON `products`.`id` = `companies_products`.`product_id`
WHERE `products`.`id` = 1
...is executed from this code inside your 2nd foreach:
$product->company->get();
In one of the coming versions, I plan on including “related_{clause}” methods to help developers fine tune their queries on related objects, to reduce the total number of queries needed. At the moment, doing the type of thing you have in your show_jobs() method might indeed generate a fair amount of queries, if there are lots of records. Even so, databases are designed to handle JOIN’s very quickly so will cope fine with this sort of database querying.
I’d recommend you page your results though, rather than just showing everything, which will save on unnecessary bandwidth usage. Example:
$page = 4;
$limit = 10;
$offset = ($limit * $page) - $limit;
$j = new Job();
$j->get($limit, $offset);
That will return a maximum of 10 records, starting at page 4 (so records 40 to 50). You can easily do this with your related items as well.
In most cases, you wouldn’t show all of the related information of your jobs when just showing the list of jobs as you’re wasting bandwidth doing that. You should only show data when the user specifically needs or asks for it. For example, IMHO there’s no need for that 2nd foreach you have. Instead, I’d list the jobs with the company name beside it and only show the list of products relating to a job after they’ve clicked the job for the details. Otherwise, you have all this product information loaded in other jobs, when they might only care about the products of that one job. You know what I mean?
@OverZealous.com: Yeah, slicehost is nice and cheap for the amazing service they provide. I’m starting off with a 256MB slice until I get everything setup and see what the performance logs say. It’s good to be able to easily grow your slice as needed and their biggest one sure is meaty, lol!
Although it’s very hands on and can take up a fair amount of time to configure and maintain, the level of control you get far out ways the downsides for me. It’s still early days for me on there though so I’ll let you know how I find it in the longer run.