Doctrine DQL: how to select objects from two tables with different conditions?

Question

symfony

There are two entity classes, I want to select objects from their tables with one query:

SELECT d, e 
        FROM OrganizerBundle:DailyTask d, OrganizerBundle:Event e
        WHERE d.weekday = 'all' OR DATE_FORMAT(e.date,'%Y-%m-%d') = :date

As you can see, I want to select a DailyTask that has weekday = 'all' and an Event that has date = :date . The problem is that the WHERE clause only works for the first table, while all elements are selected from the second.
How to register so that the conditions are separate for two tables? UNION not suggested - Doctrine does not support.
Here is the SQL query that is generated as a result:

ELECT d0_.weekday AS weekday_0, d0_.time AS time_1, d0_.length AS length_2, d0_.id AS id_3, d0_.name AS name_4, d0_.description AS description_5, e1_.date AS date_6, e1_.length AS length_7, e1_.id AS id_8, e1_.name AS name_9, e1_.description AS description_10 FROM daily d0_, events e1_ WHERE (d0_.weekday = 'all') OR (DATE_FORMAT(e1_.date,'%Y-%m-%d') = '2017-07-08')

I don’t remember something, as well as in MySQL without using UNION, such a trick can be done.

Answer 1

If you need a result - then two requests. If you have tonsils through your ass, then docs.doctrine-project.org/en/latest/reference/nati...

Answer 2

The problem is that the WHERE clause only works for the first table, while all elements are selected from the second.

The conclusion is incorrect. Both conditions are met, guaranteed by seqscan for events, if you're lucky then index scan for weekday. And as a result, as it is written in the request, the samples are multiplied among themselves in a cross join.
If the tables are not related and you need a flat list, then union all is needed here. The stupid doctrine does not know how. Possible methods of perversion without union the silly doctrine is not able especially.
So either teach the doctrine to do union all (probably non-trivial)
or make two queries (you still have seqscan now, so you don’t need performance and no one has looked at explain)
or make a view in the database with union all and go to it instead of tables ( and check if the scheduler of each of the supported bases is able to pass the conditions of the external request into views with union all)
or use a native query with normal SQL
PS: yes, I worked with the doctrine and that is why I really dislike it for the limited syntax, while still with mercilessly current abstractions and the need to know and take into account how exactly each supported database differs from others. And what for this doctrine then generally eats so much cpu and memory?

Answer 3

How to register so that the conditions are separate for two tables? UNION not suggested - Doctrine does not support.
And now what if it supports? Doctrine - does not solve this problem, since it is just a regular query builder. The problem is solved here by SQL. In such cases, you can use RAW query, discarding perfectionism:

$connection = $em->getConnection();
$query = $connection->prepare("SELECT field1, field2 FROM table1 
                                UNION
                                SELECT field3, field4 FROM table2 
                                UNION 
                                SELECT field5, field6 FROM table3
                                ");
$query->execute();
$result = $query->fetchAll();