stephen-makrogianni
2/2/2020 - 10:08 PM

SQL snippets

Fetch records from a table that have multiple IDs

SELECT *
FROM GalleryImage_Tags AS img1
INNER JOIN GalleryImage_Tags AS img2 ON img1.GalleryImageID = img2.GalleryImageID
INNER JOIN GalleryImage_Tags AS img3 ON img1.GalleryImageID = img3.GalleryImageID
WHERE img1.TaxonomyTermID = 1 AND img2.TaxonomyTermID = 5 AND img3.TaxonomyTermID = 6

Silverstripe specific. Remove duplicate records

$results = array_unique(
  DB::query(
    "SELECT *
    FROM GalleryImage_Tags AS img1
    INNER JOIN GalleryImage_Tags AS img2 ON img1.GalleryImageID = img2.GalleryImageID
    INNER JOIN GalleryImage_Tags AS img3 ON img1.GalleryImageID = img3.GalleryImageID
    WHERE img1.TaxonomyTermID = 1 AND img2.TaxonomyTermID = 5 AND img3.TaxonomyTermID = 6"
  )->map()
);