Archives par mot-clé : sql

SQL: Exclusion de résultats avec enregistrement en jointuresSQL: Exclude results with join records

Selon le cas de figure suivant: Nous avons des éléments lié a des couleurs.

Table element

id # name #
1     Sea
2     tree

Table colour

id # name #
1     green
2     blue
3     brown

Table relation

element_id # colour_id
1                 2
2                 1
2                 3

Pour répondre a la demande:

Obtenir les éléments ayant une ou plus des couleurs suivantes

Il nous suffit d’effectuer cette requête (pour les couleurs green et brown):

SELECT element.name, colour.name FROM element
LEFT JOIN relation
ON (element.id = relation.element_id)
LEFT JOIN colour
ON (colour.id = relation.colour_id)
WHERE (relation.colour_id = 1 OR relation.colour_id = 3)

Mais si nous souhaitons répondre a la demande:

Obtenir les éléments ayant toutes les couleurs suivantes

Nous allons devoir utiliser une méthode différente. Ici nous allons utiliser une première requête en utilisant un having count pour isoler les element.id qui ont le nombre de relations (vers les couleurs) suffisantes. Donc pour les couleurs green et brown:

SELECT relation.element_id FROM relation
WHERE relation.colour_id IN (1, 3)
GROUP BY relation.element_id
HAVING COUNT (DISTINCT relation.colour_id) = 2

Nous obtiendrons les element_id qui ont bien deux relations vers couleur, sachant que nous avons filtrer ces relation pour n’être que celle vers le green et le brown. Il n’y a ensuite plus qu’a récupérer nos éléments:

SELECT element.name, colour.name FROM element
WHERE element.id IN (#Ids récupérés#)

In the next case: Elements have colours.

Table element

id # name #
1     Sea
2     tree

Table colour

id # name #
1     green
2     blue
3     brown

Table relation

element_id # colour_id
1                 2
2                 1
2                 3

If we want:

Get elements who have one or more folowing colors

We just have to write this query (for green and brown):

SELECT element.name, colour.name FROM element
LEFT JOIN relation
ON (element.id = relation.element_id)
LEFT JOIN colour
ON (colour.id = relation.colour_id)
WHERE (relation.colour_id = 1 OR relation.colour_id = 3)

But if we need:

Get elements with all following colors

We have to use différent strategy. We use an first query with an having count. So with green and brown:

SELECT relation.element_id FROM relation
WHERE relation.colour_id IN (1, 3)
GROUP BY relation.element_id
HAVING COUNT (DISTINCT relation.colour_id) = 2

We will get element_id who have two relation with colours. Knowing that we have filter these relation in a way to exclude other colors. After we just have to get our elements:

SELECT element.name, colour.name FROM element
WHERE element.id IN (#Ids#)

Doctrine 2: Do an pure SQL query

Il est parfois nécessaire lorsque l’on utilise Doctrine 2 d’effectuer une requête SQL et non une requête DQL. Pour cela nous pouvons utiliser la méthode createNativeQuery dans laquelle nous pouvons utiliser le langage SQL.

// La requête SQL
$sql = "SELECT u.address FROM user u WHERE u.sex = 'M' ";
// Construction de l'objet ResultSetMapping
$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
// On définie quel champs doit être retourné dans la réponse
$rsm->addScalarResult('address', 'address');
// On récupère les résultats
$addresss = $this->getEntityManager()
->createNativeQuery($sql, $rsm)
->getScalarResult()
;

Plus d’information sur la doc officielle: http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/native-sql.html

Sometimes we have to make an SQL query instead of DQL query. For that we can use createNativeQuery method for write in SQL.

// The SQL query
$sql = "SELECT u.address FROM user u WHERE u.sex = 'M' ";
// Building of ResultSetMapping
$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
// Definition of the needed fields (they will in the result array)
$rsm->addScalarResult('address', 'address');
// We get back the results
$addresss = $this->getEntityManager()
->createNativeQuery($sql, $rsm)
->getScalarResult()
;

More documentation on official website: http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/native-sql.html