I have the following query generated with the Typeorm querybuilder

Current behavior: Right now what is happening is that marines are correctly filtered but selected joins are also filtered by the given condition. returning only the dock that matches the condition instead of all the docks that belong to the marine.

Desired result: I want to obtain all marines that have at least one dock whose name is 'dock 1'. And I want to include all docks belonging to that marine without taking their name into account.

How should i execute this query?

Any help would be much appreciated.

mariadb Ver 15.1

[email protected]

SELECT `c`.`id`                AS `c_id`,
       `c`.`name`              AS `c_name`,
       `c`.`description`       AS `c_description`,
       `c`.`FkId`              AS `c_FkId`,
       `c`.`FkModel`           AS `c_FkModel`,
       `c`.`isActive`          AS `c_isActive`,
       `c`.`createdAt`         AS `c_createdAt`,
       `c`.`updatedAt`         AS `c_updatedAt`,
       `c`.`phones`            AS `c_phones`,
       `c`.`emails`            AS `c_emails`,
       `c`.`mainImage`         AS `c_mainImage`,
       `c`.`galleryImages`     AS `c_galleryImages`,
       `c`.`addressDetailsId`  AS `c_addressDetailsId`,
       `c_Docks`.`id`          AS `c_Docks_id`,
       `c_Docks`.`name`        AS `c_Docks_name`,
       `c_Docks`.`description` AS `c_Docks_description`,
       `c_Docks`.`FkId`        AS `c_Docks_FkId`,
       `c_Docks`.`FkModel`     AS `c_Docks_FkModel`,
       `c_Docks`.`isActive`    AS `c_Docks_isActive`,
       `c_Docks`.`createdAt`   AS `c_Docks_createdAt`,
       `c_Docks`.`updatedAt`   AS `c_Docks_updatedAt`,
       `c_Docks`.`MarineId`    AS `c_Docks_MarineId`
FROM `marine` `c`
         LEFT JOIN `dock` `c_Docks` ON `c_Docks`.`MarineId` = `c`.`id`
WHERE `c`.`isActive` = true
  AND (`c_Docks`.`name` = 'dock 1')
# ORDER BY c_id DESC

CodePudding user response：

Step 1: Filter out all the rows relevant from table marine: select from marine C where c.isActive = true
Step 2: Make the earlier SQL an inline view and left join it with table dock and apply the filter on dock table. So, the SQL should look like this:

SELECT          c.<column_names> ,
                c_docks.<column_names>
FROM            (
                       SELECT <column_names>
                       FROM   marine
                       WHERE  isActive = true) c
LEFT OUTER JOIN dock c_docks
ON              c.id = c_docks.marine_id
AND             c_docks.NAME = 'dock 1';

CodePudding user response：

You can join with dock twice. Once to filter on the dock name, and another time to get all docks.

SELECT `c`.`id`                AS `c_id`,
       `c`.`name`              AS `c_name`,
       `c`.`description`       AS `c_description`,
       `c`.`FkId`              AS `c_FkId`,
       `c`.`FkModel`           AS `c_FkModel`,
       `c`.`isActive`          AS `c_isActive`,
       `c`.`createdAt`         AS `c_createdAt`,
       `c`.`updatedAt`         AS `c_updatedAt`,
       `c`.`phones`            AS `c_phones`,
       `c`.`emails`            AS `c_emails`,
       `c`.`mainImage`         AS `c_mainImage`,
       `c`.`galleryImages`     AS `c_galleryImages`,
       `c`.`addressDetailsId`  AS `c_addressDetailsId`,
       `c_Docks`.`id`          AS `c_Docks_id`,
       `c_Docks`.`name`        AS `c_Docks_name`,
       `c_Docks`.`description` AS `c_Docks_description`,
       `c_Docks`.`FkId`        AS `c_Docks_FkId`,
       `c_Docks`.`FkModel`     AS `c_Docks_FkModel`,
       `c_Docks`.`isActive`    AS `c_Docks_isActive`,
       `c_Docks`.`createdAt`   AS `c_Docks_createdAt`,
       `c_Docks`.`updatedAt`   AS `c_Docks_updatedAt`,
       `c_Docks`.`MarineId`    AS `c_Docks_MarineId`
FROM `marine` `c`
JOIN `dock` `c_Docks` ON `c_Docks`.`MarineId` = `c`.`id`
JOIN `dock` `dock_1` ON `dock_1`.`MarineId` = `c`.`id`
WHERE `c`.`isActive` = true
  AND `dock_1`.`name` = 'dock 1'

There's no reason to use LEFT JOIN here, since you don't want to include marines with no matching dock.