I am having a table called listings. A listing can have 2 images, a front and a back-image.

I currently join the tables and get the following output:

**Schema (MySQL v5.7)**

    CREATE TABLE `images` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `listings_id` int(11) DEFAULT NULL,
      `file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `properties` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `listings` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `price` double(8,2) NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    
    INSERT INTO images (id, listings_id, file_name, properties, created_at, updated_at) VALUES(1, 1, 'trv_front.png', '[{"front_back":"front","path":null}]', '2021-10-16 07:08:06', '2021-10-16 07:08:06');
    INSERT INTO images (id, listings_id, file_name, properties, created_at, updated_at) VALUES(2, 2, '20211016070806_trv.png', '[{"front_back":"front","path":null}]', '2021-10-16 07:08:06', '2021-10-16 07:08:06');
    INSERT INTO images (id, listings_id, file_name, properties, created_at, updated_at) VALUES(3, 1, '2_rv.png', '[{"front_back":"back","path":null}]','2021-10-16 07:08:06', '2021-10-16 07:08:06');
    INSERT INTO images (id, listings_id, file_name, properties, created_at, updated_at) VALUES(4, 2, 'backd.png', '[{"front_back":"back","path":null}]','2021-10-16 07:08:06', '2021-10-16 07:08:06');
    
    INSERT INTO listings (id, title, price, created_at, updated_at) VALUES(1, 'test1', 10, '2021-10-16 08:14:08', '2021-10-16 08:14:08');
    INSERT INTO listings (id, title, price, created_at, updated_at) VALUES(2, 'test2', 42, '2021-10-16 08:14:08', '2021-10-16 08:14:08');
    INSERT INTO listings (id, title, price, created_at, updated_at) VALUES(3, 'test3', 43, '2021-10-16 08:14:08', '2021-10-16 08:14:08');
    INSERT INTO listings (id, title, price, created_at, updated_at) VALUES(4, 'test4', 50, '2021-10-16 08:14:08', '2021-10-16 08:14:08');

---

**Query #1**

    select l.id, l.title, i.file_name, i.listings_id, i.properties from listings l
    left join images i on i.listings_id = l.id order by l.id;

| id  | title | file_name              | listings_id | properties                           |
| --- | ----- | ---------------------- | ----------- | ------------------------------------ |
| 1   | test1 | trv_front.png          | 1           | [{"front_back":"front","path":null}] |
| 1   | test1 | 2_rv.png               | 1           | [{"front_back":"back","path":null}]  |
| 2   | test2 | 20211016070806_trv.png | 2           | [{"front_back":"front","path":null}] |
| 2   | test2 | backd.png              | 2           | [{"front_back":"back","path":null}]  |
| 3   | test3 |                        |             |                                      |
| 4   | test4 |                        |             |                                      |

As you can see in the properties-column there I can see if the image is front or the back side.

However, I would like to get the following structure adding the two columns front_image and back_image:

| id | title | file_name              | listings_id | front_image             | back_image |
|----|-------|------------------------|-------------|-------------------------|------------|
| 1  | test1 | trv_front.png          | 1           | trv_front.png           | 2_rv.png   |
| 2  | test2 | 20211016070806_trv.png | 2           | 220211016070806_trv.png | backd.png  |
| 3  | test3 |                        |             |                         |            |
| 4  | test4 |                        |             |                         |            |

This is my dbfiddle.

Any suggestions how to add the image columns?

I appreciate your replies!

CodePudding user response：

Please use the case in your sql query : Example is given below.

select l.id, l.title, i.file_name, i.listings_id, 
    CASE
        WHEN i.properties = '[{"front_back":"front","path":null}]' THEN i.file_name
    END AS front_image,
    CASE
        WHEN i.properties = '[{"front_back":"back","path":null}]' THEN i.file_name
    END AS back_image,
      i.properties from listings l
    left join images i on i.listings_id = l.id order by l.id

CodePudding user response：

Make use of JSON function in MySQL, JSON_EXTRACT(json_doc, path[, path] ...)

Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression.

SELECT 
  a.id, 
  MAX(title) AS title, 
  GROUP_CONCAT(b.file_name) AS file_names, 
  MAX(b.listings_id) AS listings_id, 
  MAX(CASE WHEN JSON_EXTRACT(b.properties, '$[0].front_back') = 'front' THEN b.file_name ELSE NULL END) AS front_image,
  MAX(CASE WHEN JSON_EXTRACT(b.properties, '$[0].front_back') = 'back' THEN b.file_name ELSE NULL END) AS back_image
FROM listings a
LEFT JOIN images b ON a.id = b.listings_id
GROUP BY a.id

CodePudding user response：

Try this:

Query #1

SELECT
    Id,
    Title,
    File_Name,
    listings_id,
    MIN(front_image),
    MIN(back_image)
FROM 
(
  SELECT
    l.id,
    l.title,
    i.file_name,
    i.listings_id, 
      CASE
          WHEN i.properties = '[{"front_back":"front","path":null}]' THEN i.file_name
      END AS front_image,
      CASE
          WHEN i.properties = '[{"front_back":"back","path":null}]' THEN i.file_name
      END AS back_image,
        i.properties FROM listings l
      LEFT JOIN images i ON i.listings_id = l.id
  ORDER BY l.id
) AS t1 GROUP BY id;

View on DB Fiddle