Des grands lacs aux rivières sauvages, jetez votre ligne au cœur d'une nature préservée !

Pêche

24p.pid, p.status, p.part, p.title, p.ancre, p.uri, m.*,ag.lat,ag.lng,ad.addplab,ad.addpcode,ac.adctlab,ast.adst_1,ast.adst_2,ast.adst_3,ac.adctcode,pcl.inc,pcl.cat,pr.stars, ST_DISTANCE_SPHERE(POINT(ag.lng, ag.lat), POINT(0, 0)) AS dist_m, COUNT(*) OVER() AS tot FROM _p p LEFT JOIN _media m ON p.m_id = m.mid JOIN _ad_rel ar ON p.ad_id = ar.adid JOIN _ad_geo ag ON ar.adge_id = ag.adgeid JOIN _ad_ct ac ON ar.adct_id = ac.adctid JOIN _ad_dp ad ON ar.addp_id = ad.addpid LEFT JOIN _ad_st ast ON ar.adst_id = ast.adstid LEFT JOIN ( SELECT r.p_id, MAX(c.cid = 1769) AS inc, MIN(CASE WHEN c.cid NOT IN (1769,1787) THEN c.label END) AS cat FROM _rel_p_cl r JOIN _cl c ON r.cl_id1 = c.cid WHERE r.cl_typ = 1 GROUP BY r.p_id ) pcl ON pcl.p_id = p.pid LEFT JOIN ( SELECT p_id, MAX(CASE cl_id1 WHEN 690 THEN 1 WHEN 695 THEN 2 WHEN 700 THEN 3 WHEN 705 THEN 4 WHEN 710 THEN 5 END) AS stars FROM _rel_p_cl WHERE cl_typ = 3 AND cl_id1 IN (690,695,700,705,710) GROUP BY p_id ) pr ON pr.p_id = p.pid WHERE p.typ=2 AND p.status=1 AND EXISTS ( SELECT 1 FROM _rel_p_cl rpc WHERE rpc.p_id=p.pid AND rpc.cl_typ=1 AND ( rpc.cl_id1 IN (2198) OR rpc.cl_id2 IN (2198) OR rpc.cl_id3 IN (2198) OR rpc.cl_id4 IN (2198) ) ) AND EXISTS ( SELECT 1 FROM _rel_p_cl rpc WHERE rpc.p_id=p.pid AND rpc.cl_typ=5 AND ( rpc.cl_id1 IN (1747) OR rpc.cl_id2 IN (1747) OR rpc.cl_id3 IN (1747) OR rpc.cl_id4 IN (1747) ) ) AND EXISTS ( SELECT 1 FROM _rel_p_cl rpc WHERE rpc.p_id=p.pid AND rpc.cl_typ=7 AND ( rpc.cl_id1 IN (1770) OR rpc.cl_id2 IN (1770) OR rpc.cl_id3 IN (1770) OR rpc.cl_id4 IN (1770) ) ) ORDER BY COALESCE(pcl.inc,0) DESC, p.part DESC, dist_m ASC LIMIT 0, 10