Fetch single product image when having multiple images

Question

My use case is similar to the here. For some reason my code is not working All I want to do is, fetch products, along with it a single product image

Below is the response I receive when I run the query, but I want single row (here id=10 is returned twice because there is two image for the product)

[
  {
        "id": "10",
        "title": "Horlicks Health & Nutrition Drink Jar",
        "image": "url-8.1"
    },
    {
        "id": "10",
        "title": "Horlicks Health & Nutrition Drink Jar",
        "image": "url-8.2"
    }
]

I tried to run this, it works but without GROUP BY

SELECT p.*,ph.url
FROM  products AS p
LEFT JOIN images AS ph 
ON p.id=ph.product_id 
WHERE (p.title LIKE '%hor%')
GROUP BY p.id

Error : Trying to get property 'num_rows' of non-object

Schema

CREATE TABLE `images` (
  `id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `images` (`id`, `product_id`, `url`) VALUES
(1, 8, 'url-8.1'),
(8, 10, 'url-8.11'),
(9, 10, 'url-8.000');



CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `products` (`id`, `title`) VALUES
(8, 'Bbetter Vitamin'),
(9, 'Liveasy Essentials'),
(10, 'Horlicks Health');


ALTER TABLE `images`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

---

This query also works but it uses subquery, it be cause perfomance bottleneck as its a search API having lots of search conditions

SELECT p.*, ph.url as image
FROM products AS 
LEFT JOIN images AS ph
ON ph.id=(SELECT id FROM images AS pi WHERE pi.product_id = p.id LIMIT 1)
WHERE (p.title LIKE '%hor%')

This query also works but I need ph.url, if I add it then it gives me error. This works SELECT p.*, MIN(ph.url) as image but I want to fetch the first image which matched

SELECT p.*, MIN(ph.id) as image_id
FROM products AS p
LEFT JOIN images AS ph
ON p.id=ph.product_id 
WHERE (p.title LIKE '%hor%')
GROUP BY p.id

Answer 1

Error : Trying to get property 'num_rows' of non-object

This is coming from the code you are using to run the query. The query is invalid, so it returns an error. You're trying to use that error as a result. Probably something like...

result = query.execute(...)
print result.num_rows

You need to check the query execution for an error. I can't tell you how to do that, you'll need to consult the documentation for your query library.

---

I want to fetch the first image which matched

SQL has no inherent order. If you have an idea of which is "first" you need to add an order by clause.

If you just want one result and it doesn't matter which one, use limit 1.

---

Now, why doesn't this work...

SELECT p.*,ph.url
...
GROUP BY p.id

And why does this work?

SELECT p.*, MIN(ph.id) as iid
...
GROUP BY p.id

When you use a group by, all non-grouped columns are aggregated into a single result for each thing in the group. You need to tell SQL how to do that aggregation using aggregate functions like min, max, sum and so on.

The first doesn't work because for each id there are multiple urls and you didn't tell SQL how to aggregate the urls. This is the SQL error which you'll see once you check your queries for errors in your code.

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ph.url' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

You can fix this by applying an aggregate function to ph.url. min will work and will return the first URL alphabetically.

SELECT p.*, MIN(ph.url)
...
GROUP BY p.id

---

However, group by is not actually returning the first row. It's returning a all rows with the same ID smashed together. If you just want one row, use a limit.

SELECT p.*, ph.url
FROM products AS p
LEFT JOIN images AS ph
ON p.id=ph.product_id 
WHERE (p.title LIKE '%hor%')
limit 1

Because SQL has no inherent order, this will return any matching row. You cannot rely on the ordering. If you need it to be in a specific order, add an order by.

For example, order by ph.id asc will get the image with the lowest ID; presumably this is the first.

SELECT p.*, ph.url
FROM products AS p
LEFT JOIN images AS ph
ON p.id=ph.product_id 
WHERE (p.title LIKE '%hor%')
order by ph.id asc
limit 1

While the order of IDs are somewhat reliable for the order in which the row was inserted, it is not reliable for the ordering according to business rules. For example, if one imports data from another source often those items might have been created in a different order than they're inserted.

Typically one adds a created_at timestamp to their tables, defaulting to current_timestamp, to allow ordering.

---

Demonstration of all that.