We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

PHQL left join adds same table twice

Hello. I am trying to execute following PHQL: $weekAgoDate = date('Y-m-d', time()-604800);

    $phql = "SELECT
        DISTINCT(s_d.id) as id, s_d.host
    FROM
        OffersDomains o_s_d, Domains s_d
    LEFT JOIN
        ZpDomainStatsUpdated AS d_u
    ON
        s_d.id = d_u.domain_id
    WHERE
    (   d_u.date_updated < '$weekAgoDate'
    OR
        d_u.date_updated IS NULL )
    AND
        o_s_d.domain_id = s_d.id
    AND
        host != ''
    ORDER BY
        d_u.date_updated IS NULL DESC, s_d.id ASC
    LIMIT 1
    ";

I attached a listener and saw the query was converted to following sql:

SELECT DISTINCT (s_d.id) AS id,  s_d.host AS host
FROM offers_domains AS o_s_d,
domains AS s_d
LEFT JOIN zp_domain_stats_updated AS d_u ON s_d.id = d_u.domain_id    
LEFT JOIN zp_domain_stats_updated AS d_u ON s_d.id = d_u.domain_id
WHERE (d_u.date_updated < '2016-01-12'
OR d_u.date_updated IS NULL)
AND o_s_d.domain_id = id
AND s_d.host <> ''
ORDER BY d_u.date_updated IS NULL DESC, id ASC LIMIT 1

As you can see, the zp_domain_stats_updated table joined twice for some reason. How can I avoid this?

Show us your relations and joins in model manager.

edited Jan '16

https://screencast.com/t/qHI5QwyjcDs

Show us your relations and joins in model manager.