| Autor | Zpráva | ||
|---|---|---|---|
| jval Profil * |
#1 · Zasláno: 27. 5. 2013, 20:59:33
Zdravím, mám takovýto SQL dotaz:
$sql = "SELECT p.*,p.id as pid,l.*,u.realname,i.*,r.title as region, c.title as city
FROM " . $db_prefix . "products p
LEFT JOIN " . $db_prefix . "products_lang l
ON p.id=l.product_id
LEFT JOIN (SELECT product_id,image as image
FROM " . $db_prefix . "products_images
ORDER BY ord) i
ON p.id = i.product_id
LEFT JOIN " . $db_prefix . "cities_lang c
ON p.city_id=c.city_id
LEFT JOIN " . $db_prefix . "users u
ON p.user_id=u.id
LEFT JOIN (SELECT rr.id as rr_id,rl.title
FROM " . $db_prefix . "regions rr
LEFT JOIN " . $db_prefix . "regions_lang rl
ON rr.id = rl.region_id) r
ON r.rr_id = p.region_id
WHERE l.lang='" . $LANG . "' AND p.deleted=0 " . $filter . "
GROUP BY p.id
ORDER BY " . $order . "
LIMIT " . $limit . "
OFFSET " . $offset . "";Díky |
||
| Kajman Profil |
#2 · Zasláno: 28. 5. 2013, 10:10:20
jval:
Musíte si pro každý produkt najít nejmenší ord viz. třetí příklad z faq. A následně podle produktu a ord připojit odpovídající obrázek. |
||
|
Časová prodleva: 3 dny
|
|||
| jval Profil * |
#3 · Zasláno: 31. 5. 2013, 21:36:23
Tak jsem to nakonec vyřešil subselectem místo JOINu
$sql = "SELECT p.*,p.id as pid,l.*,u.realname,r.title as region, c.title as city, (SELECT image FROM " . $db_prefix . "products_images
WHERE p.id = product_id
ORDER BY ord
LIMIT 1) AS image
FROM " . $db_prefix . "products p
LEFT JOIN " . $db_prefix . "products_lang l
ON p.id=l.product_id
LEFT JOIN " . $db_prefix . "cities_lang c
ON p.city_id=c.city_id
LEFT JOIN " . $db_prefix . "users u
ON p.user_id=u.id
LEFT JOIN (SELECT rr.id as rr_id,rl.title
FROM " . $db_prefix . "regions rr
LEFT JOIN " . $db_prefix . "regions_lang rl
ON rr.id = rl.region_id) r
ON r.rr_id = p.region_id
WHERE l.lang='" . $LANG . "' AND p.deleted=0 " . $filter . "
GROUP BY p.id
ORDER BY " . $order . "
LIMIT " . $limit . "
OFFSET " . $offset . ""; |
||
|
Časová prodleva: 12 let
|
|||
0