Unknow column (et pourtant si !)

[Feng-Huang]

Bonjour,

J'utilise la requête suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT * FROM campaign_has_site
LEFT JOIN site USING(site_id)
LEFT JOIN user U USING(user_id)
LEFT JOIN campaign USING(campaign_id)
LEFT JOIN available_pub USING(campaign_id)
LEFT JOIN pub USING(pub_id)
LEFT JOIN pub_format USING(pub_format_id)
WHERE 
    campaign_has_site.site_id = 2
AND pub_format_id IN (SELECT SQL_CACHE pub_format_id FROM available_format WHERE available_format.site_id = 2)
AND campaign.campaign_max_credits > (campaign.campaign_consumed + (
 
	SELECT SUM(count) FROM (
		SELECT CEIL(COUNT(*)/pub_format_display_count) AS count FROM user
		LEFT JOIN pub USING(user_id)
		LEFT JOIN pub_format USING(pub_format_id)
		LEFT JOIN available_pub USING(pub_id)
		LEFT JOIN displayed USING(available_pub_id)
		WHERE user.user_id = U.user_id
		GROUP BY pub_format_id
	) sub
	))

Et je me tape un Unknown column 'u.user_id' in 'where clause', ce que je trouve étrange, étant donné que j'ai donné l'alias plus haut ; est-ce du au fait que c'est une requête doublement imbriquée, qui n'a accès qu'aux alias du dessus ? (donc aucun alias en l'occurence)

Je pourrais très bien passer le user_id manuellement, mais je trouve plus sémantiquement correct que ce soit la requête qui donne le user_id

PS : est-ce que je peux mettre en cache la requête principale (dont le résultat va changer genre une ou deux fois par jour) sans que la sous-requête (somme) invalide le cache ??

[CinePhil]

Le problème avec USING et des jointures multiples et sans indentation, c'est qu'on ne sait pas au premier coup d'oeil quelles tables sont jointes !

Une analyse rapide de ta requête me laisse à penser que certaines jointures externes pourraient tout à fait être des jointures internes.
Puisque campaign_has_site est probablement une table associative, les jointures de celle-ci avec les tables campaign et site gagneraient à être des jointures internes.

Ce qui donnerait ce début de requête :

Code :

1
2
3
4
5
6
7
8
9
SELECT les_colonnes_necessaires_et_pas_etoile
FROM campaign_has_site cs
INNER JOIN site s ON s.site_id = cs.site_id
    INNER JOIN user U ON U.user_id = s.user_id
INNER JOIN campaign c ON c.campaign_id = cs.campaign_id
    LEFT JOIN available_pub ap ON ap.campaign_id = c.campaign_id
        LEFT JOIN pub p ON p.pub_id = ap.pub_id
            LEFT JOIN pub_format pf ON pf.pub_format_id = p.pub_format_id
WHERE cs.site_id = 2

Par contre, peut-il y avoir des campaign sans available_pub ?
Si la réponse est non, alors les autres jointures pourraient sans doute être aussi des jointures internes.
Et si c'est le cas, alors la condition AND pub_format.pub_format_id IN... peut sans doute être aussi transformée en jointure interne, ce qui sera plus performant.

Dis-nous-en un peu plus sur ce qu'est sensée retourner cette requête.

Quant à ta question principale, je pense effectivement que l'imbrication des requêtes, en plus dans une formule de calcul, doit pas mal perturber le SGBD. Là encore, il vaudrait sans doute mieux faire une jointure entre user et la sous-requête.

Mais ce ne sont que des pistes après une étude incomplète de la requête.

[Feng-Huang]

Oui j'avoue qu'en règle générale je ne me soucis pas trop de interne/externe tant que la logique est correcte.
En respectant un peu plus le truc, ça fait :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT * FROM campaign_has_site
INNER JOIN campaign ON campaign_has_site.campaign_id = campaign.campaign_id
    LEFT JOIN available_pub ON available_pub.campaign_id = campaign.campaign_id
	    LEFT JOIN pub ON available_pub.pub_id = pub.pub_id
		    LEFT JOIN pub_format ON pub_format.pub_format_id = pub.pub_format_id
INNER JOIN site ON site.site_id = campaign_has_site.site_id
    INNER JOIN user ON site.user_id = user.user_id
WHERE 
    campaign_has_site.site_id = :site_id
AND pub_format.pub_format_id = :pub_format_id
AND campaign.campaign_max_credits > (campaign.campaign_consumed + (
 
    SELECT COALESCE( SUM(count) , 0 ) FROM (
	    SELECT CEIL(COUNT(*)/pub_format_display_count) AS count FROM user
	    LEFT JOIN pub USING(user_id)
	    LEFT JOIN pub_format USING(pub_format_id)
	    LEFT JOIN available_pub USING(pub_id)
	    LEFT JOIN displayed USING(available_pub_id)
	    WHERE user.user_id = :user_id
	    GROUP BY pub_format_id
	) sub
	))
AND pub.pub_is_porn <= site.site_allow_porn

En français la requête est :

Obtenir la liste des pubs appartenant à au moins une campagne qui a sélectionnée le site SITE_ID et dont la consommation n'a pas atteint son maximum.

Le nombre de crédit actuellement consommé est égal au champ campaign_consumed (absent sur le schéma qui n'est plus tellement à jour) + la somme de : [le nombre de ligne dans displayed divisé par le nombre d'affichage par crédit pour chaque type de pub] arrondi au supérieur

J'aurais voulu enregistrer ce message pour le continuer plus tard, je le publie et j'éditerais au besoin