SQL query based on condition

user_1094850 0
Viewed 120

Use case: I am working on a sql query for the below tables. I have 2 tables in my schema viz cropdetails and translationcropdetails. Cropdetails holds the basic common info and translationcropdetails holds the translations in multiple languages.

I am working on writing a query that would return a list of all records from translationcropdetails with language id =<english_lang_id> and also return in how many languages the record is present in the table.

   Example output:
   cropid  |  cropname    |    langid1(say eng)                           |    langid2(say spanish)          | langid3
      1       Wheat        true(if translation present for that language)   false(if translation not present)

I need direction in which I can move in writing this. I am not sure if I can go ahead with case when operator. Any inputs would be helpful in achieving the expected result.

 +--------------+--------------+------+-----+---------+----------------+
   | Field        | Type         | Null | Key | Default | Extra          |
   +--------------+--------------+------+-----+---------+----------------+
   | cropid       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
   | cropimage    | varchar(255) | NO   |     | NULL    |                |
   | noofdays     | int(11)      | NO   |     | NULL    |                |
   +--------------+--------------+------+-----+---------+----------------+
      
   +-----------------+--------------+------+-----+---------+-------+
    | Field           | Type         | Null | Key | Default | Extra |
   +-----------------+--------------+------+-----+---------+-------+
   | trcropid        | bigint(20)   | NO   | PRI | NULL    |       |
   | cropadvice      | text         | NO   |     | NULL    |       |
   | cropattacks     | text         | NO   |     | NULL    |       |
   | cropdescription | text         | NO   |     | NULL    |       |
   | cropname        | varchar(255) | NO   |     | NULL    |       |
   | cropid          | bigint(20)   | YES  | MUL | NULL    |       |
   | languageid      | bigint(20)   | YES  | MUL | NULL    |       |
   +-----------------+--------------+------+-----+---------+-------+

I am using spring boot and this is a paging query that I will be executing as a native query. Currently the below query will fetch only the list of records in english.

   @Query(value = "select cr.* from (select cr.trcropid, cr.cropname, cr.cropid, cr.languageid from "
        + "translationcropdetails cr where cr.languageid=:languageId) cr \n#pageable\n",
        countQuery = "select count(*) from (select cr.trcropid, cr.cropname, cr.cropid, cr.languageid from "
                + "translationcropdetails cr where cr.languageid=:languageId) temp", nativeQuery = true)
public Page<Object[]> getAllCrops(@Param("languageId") Long languageId, Pageable pageable);

Need the way to achieve the result as mentioned above.

resultset mysql sql
1 Answers

You can try with this query, which builds another query that will retreive the resultset you need. It seems a strange approach, but with MySQL is the only way to have the final query built dynamically, without know how many languages you have defined.

    SELECT CONCAT(
        'SELECT `translationcropdetails`.cropid', GROUP_CONCAT(', `t_', REPLACE(languageid, '`', '``'), '`.trcropid IS NOT NULL AS `lang_', REPLACE(languageid, '`', '``'), '`' SEPARATOR ''),
 ' FROM `translationcropdetails` ', GROUP_CONCAT('
     LEFT JOIN `translationcropdetails`   AS `t_', REPLACE(languageid, '`', '``'), '`
            ON `translationcropdetails`.cropid = `t_', REPLACE(languageid, '`', '``'), '`.cropid
           AND `t_', REPLACE(languageid, '`', '``'), '`.languageid = ', QUOTE(languageid)
     SEPARATOR ''),
 ' GROUP BY `translationcropdetails`.cropid'
) FROM (SELECT DISTINCT languageid FROM `translationcropdetails`) t;

and this is the query built from the previous one in case of 3 languages defined:

SELECT
    `translationcropdetails`.cropid,
    `t_1`.trcropid IS NOT NULL AS `lang_1`,
    `t_2`.trcropid IS NOT NULL AS `lang_2`,
    `t_3`.trcropid IS NOT NULL AS `lang_3`
FROM `translationcropdetails`
    LEFT JOIN `translationcropdetails` AS `t_1`
        ON `translationcropdetails`.cropid = `t_1`.cropid
        AND `t_1`.languageid = '1'
    LEFT JOIN `translationcropdetails` AS `t_2`
        ON `translationcropdetails`.cropid = `t_2`.cropid
        AND `t_2`.languageid = '2'
    LEFT JOIN `translationcropdetails` AS `t_3`
        ON `translationcropdetails`.cropid = `t_3`.cropid
        AND `t_3`.languageid = '3'
GROUP BY `translationcropdetails`.cropid;

You can see the result of the generated query in SQLFiddle where I used the following dummy data:

-- languageid: 1 = English | 2 = Spanish | 3 = French | ...
INSERT INTO translationcropdetails VALUES
  (1, 'adv 1', 'att 1', 'english descr', 'name 1', 1, 1),
  (2, 'adv 1', 'att 1', 'español descr', 'name 1', 1, 2),
  (3, 'adv 2', 'att 2', 'eng descr', 'name 2', 2, 1),
  (4, 'adv 2', 'att 2', 'descr en français', 'name 2', 2, 3),
  (5, 'adv 3', 'att 3', 'eng descr', 'name 3', 3, 1),
  (6, 'adv 4', 'att 4', 'eng descr', 'name 4', 4, 1),
  (7, 'adv 5', 'att 5', 'español descr', 'name 5', 5, 2),
  (8, 'adv 5', 'att 5', 'descr en français', 'name 5', 5, 3)
 ;

The output will be

cropid  lang_1  lang_2  lang_3
------- ------- ------- -------
1       1       1       0
2       1       0       1
3       1       0       0
4       1       0       0
5       0       1       1
user_150111540
Related