Złożone zapytanie SQL do 3 tabel z filtrowaniem sortowaniem oraz stronnicowaniem

Question

Witam mam kłopot z zapytaniem które pobiera mi listę projektów według określonych reguł.

Zapytanie wygląda tak

SELECT projects.id, projects.name, projects.intro, projects.start_date, projects.priority 
FROM projects 
INNER JOIN join_groups ON join_groups.user_id = 1
INNER JOIN allocations ON allocations.user_id = 1 
WHERE 1 AND (projects.only_group = 0 OR (projects.only_group = join_groups.group_id AND join_groups.user_id = 1)) 
ORDER BY projects.start_date DESC, projects.id 
LIMIT 10 
OFFSET 0

problem polega na tym, że nie bardzo wiem jak połączyć te tabele. Jeśli w dołączonych tabelach znajduje się potencjalnie poprawny wynik to wszystko działa jeśli jednak w dołączonych tabelach nie ma żadnego rekordu nic się nie wyświetla.

Zapytanie jest generowane przez skrypt w php
 

   public function get_project_list($args) {
        $select = '
SELECT projects.id, projects.name, projects.intro, projects.start_date, projects.priority';
        $from = ' 
FROM projects 
INNER JOIN join_groups ON join_groups.user_id = ' . $args['user_id'] . '
INNER JOIN allocations ON allocations.user_id = ' . $args['user_id'];
        
        $where = ' 
WHERE ' . $args["filter"] . ' AND (projects.only_group = 0 OR (projects.only_group = join_groups.group_id AND join_groups.user_id = ' . $args['user_id'] . '))';

        $like = $args["phrase"] != '' ? ' 
AND (projects.name LIKE "%' .$args["phrase"] . '%" OR projects.client LIKE "%' .$args["phrase"] . '%")'
        : '';

        $direction = (int)$args["direction"] == 1 ? ' DESC' : ''; 

        $orderby = ' 
ORDER BY projects.' . $args["sort"] . $direction . ', projects.id';
        
        $offset = ' 
OFFSET ' . $args["offset"];
        $limit = ' 
LIMIT ' . $args["limit"];

        try{
            $query = $this->connect->prepare(
                $select .$from . $where . $like . $orderby . $limit . $offset
            );
            $query->execute();
        } catch(PDOException $error) {
            exit('Błąd łączenia z bazą danych');
        }

Regóły sortowania oraz filtrowania są pobierane z pliku konfiguracyjnego w innym skrypcie i przekazywane do tej funkcji
 

"filter" => [
        "1",
        "projects.end_date IS NULL",
        "projects.end_date IS NOT NULL",
        "projects.priority != 0",
        "projects.settled = 0",
        "projects.id = allocations.project_id AND allocations.user_id = " . $_SESSION["user_id"]
    ],
"sort" => [
        "start_date",
        "end_date",
        "name",
        "priority",
        "type",
        "client"
    ]

Struktura tych 3 tabel wygląda tak:

tabela projects

 

Tabela join_groups

 

Tabela allocations

No i moje pytanie jak to połączyć żeby to działało nawet jak w tych dołączonych tabelach nie ma wpisu z odpowiednim id użytkownika?

Answer 1

Znalazłem rozwiązanie, INNER JOIN zwraca część wspólną z wszystkich 3 tabel dlatego kiedy wymaganego elementu nie ma w jednej z tabel nic się nie wyświetli. LEFT JOIN zwraca wszystkie rekordy z tabeli po lewej czyli w tym wypadku projects niezależnie czy w dołączanych tabelach są pasujące dane czy też nie. Dlatego rozwiązaniem jest zastosowanie LEFT JOIN zamiast INNER JOIN.