Shoot me now, I need JOIN help

[eluser]slowgary[/eluser]
Or at least I think I need JOIN help. I have a table of products, and a table of product images, each with a corresponding prod_id field.

How can I select * from products, and also tack on 1 image for each product?

In other words, when looking at an individual product in detail view, I'll select * for that product, plus select * images for that product. My problem is when I want to list all products in a category. I can select * from products, but I don't want to run an additional query for each product (could be many) just to get an image. I don't need to get all the images for a product, just one.

I've tried many JOIN queries (which I'm terrible at) as well as a few other things. My query for getting a product list from a given category is currently pretty ugly:

Code:

$this->db->query("
   SELECT *
   FROM `shop_products`
   WHERE prod_id IN (
      SELECT `prod_id`
      FROM `shop_product_categories`
      WHERE `cat_id` = (
         SELECT `cat_id`
         FROM `shop_categories`
         WHERE `cat_name` = '$category_name' LIMIT 1
      )
   )
");

I just need to add a single image from the shop_product_images table for each row of this query. What the deuce?

[eluser]ggoforth[/eluser]
[quote author="slowgary" date="1243414700"]Or at least I think I need JOIN help. I have a table of products, and a table of product images, each with a corresponding prod_id field.

How can I select * from products, and also tack on 1 image for each product?

In other words, when looking at an individual product in detail view, I'll select * for that product, plus select * images for that product. My problem is when I want to list all products in a category. I can select * from products, but I don't want to run an additional query for each product (could be many) just to get an image. I don't need to get all the images for a product, just one.

I've tried many JOIN queries (which I'm terrible at) as well as a few other things. My query for getting a product list from a given category is currently pretty ugly:

Code:

$this->db->query("
   SELECT *
   FROM `shop_products`
   WHERE prod_id IN (
      SELECT `prod_id`
      FROM `shop_product_categories`
      WHERE `cat_id` = (
         SELECT `cat_id`
         FROM `shop_categories`
         WHERE `cat_name` = '$category_name' LIMIT 1
      )
   )
");

I just need to add a single image from the shop_product_images table for each row of this query. What the deuce?[/quote]

Not sure I quite understand, but if your table set up is like this:

shop_products <- has all your info
shop_product_images <- has your image info and links via product_id

Your query would look like:

Code:

SELECT shop_products.*,shop_product_images.field_you_need FROM shop_products JOIN shop_product_images ON shop_products.product_id = shop_product_images.product_id WHERE shop_products.product_id = $some_id

Or with active record:

Code:

$this->db->select('shop_products.*')->select('shop_product_images.field_you_need')->from('shop_products')->join('shop_product_images','shop_products.product_id = shop_product_images.product_id')->where('shop_products.product_id',$product_id)->get();

Hope that helps.

Greg

[eluser]CrazyMerlin[/eluser]
You should make that a LEFT JOIN in case a product exists without an image, otherwise the record will not be pulled.

[eluser]ggoforth[/eluser]
[quote author="CrazyMerlin" date="1243421021"]You should make that a LEFT JOIN in case a product exists without an image, otherwise the record will not be pulled.[/quote]

You are correct sir!

[eluser]slowgary[/eluser]
Thanks for your help. I tried that and many variations already but it gives me a product result for every image. So if a product has 5 images, it'll get listed 5 times.

[eluser]TheFuzzy0ne[/eluser]
Is there any reason you can't just name the images with a hash of the product name? Then there's no need to store any data about the image in the database at all. I know a lot of people strongly believe that the image should not be hashed, so another alternative might be to name the image that same as the product name, only you can run it through a function similar to url_title first, to make the product name compatible with a file name.

Code:

function get_nice_filename($str="")
{
    return strtolower(preg_replace('/[^A-Z0-9_\-\.]/i', '', strtr($str, ' ', '_')));
}

[eluser]slowgary[/eluser]
That was the topic of another thread already, using a table for product images or not. Ultimately I decided the table was the best root because the alternative would require a directory scan and looping through the entire returned array from that scan. Additionally, the database table allows for image descriptions or other info.

If there were only one image per product I would skip the table and just use a suffix of the product sku for the filename. I will likely name the images with some sort of suffix anyway, just to keep it organized.

I'm not an SQL master, but it seems that this would be a fairly common requirement from a query. So short of using a JOIN and skipping duplicate products in PHP, is there no way to query one table and tack onto each row a JOINed row LIMIT 1?

[eluser]Evil Wizard[/eluser]
Use a left join in conjunction with a group by clause

Code:

SELECT
   shop_products.*,
   shop_product_images.field_you_need
FROM
   shop_products
JOIN
   shop_product_images
ON
   shop_products.product_id = shop_product_images.product_id
WHERE
   shop_products.product_id = $some_id
GROUP BY
   shop_products.product_id;

that will return all your products and only 1 record per product regardless of how many images it may or may not have attached

[eluser]slowgary[/eluser]
Thanks Wizard, you're not so evil after all. I tried that before but kept getting errors. Not sure what I did wrong but it works now! Oh what a noob I am.

I notice that it returns the `product_image` that was inserted first into the table, even if I switch the primary ids around or switch filenames. Is there a way to select which image is retained by the GROUP BY?

Maybe I'm going about this the wrong way. Would it make sense to store an id in my products table of the primary image?

Here are the tables I'm currently working with:

Code:

products
------------------------
prod_id
prod_sku
prod_name
prod_desc
prod_price
prod_qty
prod_ship_cost
prod_ship_weight


product_images
------------------------
image_id
prod_id
image_filename
image_desc


categories
------------------------
cat_id
cat_parent_id
cat_hidden
cat_name
cat_image
cat_desc


product_categories
------------------------
cat_id
prod_id

My thought is that if I store the primary image_id in the products table I wouldn't need the GROUP BY, it might perform better, plus I would have the ability to change which image is the primary image. Is it terrible practice though to have a field in the image table pointing at products, and a field in the products table pointing at an image?

[eluser]jedd[/eluser]
Why not a boolean column in your product_images table that indicates primary or not.

SELECTs can be LIMITed to 1 WHERE product_images.primary = TRUE .. that kind of thing.