Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write UNION in Doctrine 2.0

How to write this SQL query in Doctrine 2.0 (and fetch results)?

(SELECT 'group' AS type, 
    CONCAT(u.firstname, " ", u.surname) as fullname, 
    g.name AS subject,
    user_id, 
    who_id, 
    group_id AS subject_id,
    created 
  FROM group_notification 
  JOIN users u ON(who_id = u.id) 
  JOIN groups g ON(group_id = g.id)
)

   UNION 

(SELECT 'event' AS type, 
    CONCAT(u.firstname, " ", u.surname) as fullname, 
    e.name AS subject, 
    user_id, 
    who_id, 
    event_id AS subject_id, 
    created 
  FROM event_notification 
  JOIN users u ON(who_id = u.id) 
  JOIN events e ON(event_id = e.id)
)
   ORDER BY created
like image 838
Iškuda Avatar asked Nov 11 '10 14:11

Iškuda


People also ask

What is the syntax for union?

Syntax for declaring a union is same as that of declaring a structure except the keyword struct. Note : Size of the union is the the size of its largest field because sufficient number of bytes must be reserved to store the largest sized field. To access the fields of a union, use dot(.)

What is the union command in SQL?

What Is UNION in SQL? The UNION operator is used to combine the data from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results being combined.

Can we use union in single table?

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Can we use union all in view?

UNION ALL views can be used to improve load performance where it is too expensive to maintain secondary indexes for all rows in a table. Sybase IQ lets you split the data into several separate base tables (for example, by date). You load data into these smaller tables.


1 Answers

Well, I found maybe the best solution:

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"group" = "NotificationGroup", "event" = "NotificationEvent"})
 */
class Notification {
   // ...
}

And then two classes (NotificationGroup and NotificationEvent) extending Notification:

/**
 * @Entity
 */
class NotificationGroup extends Notification {
    //...
}

/**
 * @Entity
 */
class NotificationEvent extends Notification {
    //...
}
like image 117
Iškuda Avatar answered Nov 09 '22 00:11

Iškuda