Doctrine Collection from Multiple tables? Or how to retrieve and filter Reports.

[antitoxic]

I have the task to create an admin module which will show the annual
turnover of the company from any type of income. Which means I have to
create a sorted list from several unrelated models (productsOrders,
reservations...etc.)

This can't be solved simply by inheriting a single Income model. Why?
Because of the name, price and other fields of the Report are
generated from the relations of ProductOrder or Reservation. Relations
which are different in each Model:
Reservation has Procedure.
ProductOrder has Product.

How can I do this without involving native SQL ? As far as I know
Doctrine doesn't support UNION and that is the only solution I found.

I want to be able to use DoctrineCollection and Pagers... and
filters..all of this. In my mind Report is yet another model but read-
only.

My idea:
Can't I just define a model (with all of its fields). This model will
retrieve its data from multiple tables ( other models). And the only
thing that I will have to do is a field map for each table:
Map that defines what column from Table A corresponds the my model
"name" field
Map that defines what column from Table B corresponds the my model
"name" field .

Is this possible? Any alternative?

[Kye Etherton]

Joins? You can make joins to whatever table you want (left is best) and just spesify how you want doctrine to join them. That or use the foreach method, where you get the first set of data and itterate over it, collecting the related parts and storing the bits of them you want in arrays.

[antitoxic]

Kye, what to join?
I mean I need a sorted list of 10 elements. A mix of Reservation and
ProductOrders records (i'm mentioning two models for simplicity).These
models are not related in any way.
I don't need to join them - I need to merge a sorted list.

I might have misunderstood you. If so, can you give me an example ?

[Kye Etherton]

Well without knowing how you want the report to look it's fairly hard to tell you more. But you can get each model as an array and then write an array merge which looks at the timestamps and puts them into one list in timestamp order, storing a variable telling you the object type so when you get them out you know what it is.

But without knowing the format you want out it is very hard to give any more details.

If not a single field matches between the models, you can not put them together. And even if you could, it wouldn't mean anything.

[kdizza]

Can you create a View? Doctrine will treat it as a standard table.
Then you will be able to filter on any field you want. You may have
to tweak the filters, but that's it.

[antitoxic]

Kye, Reports should look like any other module page but no
manipulation on the data. This means paging - if I make a custom array-
merging and sorting then paging won't be possible.
I see that you simply state a possible way but this method have
nothing in common with the ORM idea to have objects in the end which I
can filter.

kdizza, Can you tell me if you had any expirience with Doctrine views?
Can I bind a view to a model ?

[Kye Etherton]

SOLR it? It'll be a lot faster to query then anything doctrine can do.

Kye Etherton
IT Manager
Pimlico Plumbers
T: 020 7928 8888
F: 020 7928 3333
http://www.Pimlicoplumbers.com

The contents of this e-mail (including any attachments) are confidential, for the recipient only and may be legally privileged. If you are not the intended recipient you should not print, copy or distribute this e-mail or use it or its contents or copy or publish its contents to any other person. If you have received this e-mail in error please notify the sender immediately and delete it from your system.

[Roel Sint]

I have been searching for a similar solution and did not found much
either. Therefore I experimented by retrieving a list of id's. And
then hydrate the records individually, because of this result caching
will be probably needed if it is used in production. The result is
that you have an array, not a collection :-(, with all different
objects, because it is a pager the results are nicely sorted. Don't
know if this helps, perhaps it gives you an idea.

See:
https://github.com/beeldspraak/bsdmSocialMediaPlugin/blob/master/lib/doctrine/dmDoctrineAggregatePager.class.php
https://github.com/beeldspraak/bsdmSocialMediaPlugin/blob/master/README
(see part "frontend, example implementation of an aggregated list")

Regards,

Roel

[antitoxic]

I found a way with minimum changes but I haven't finished coding it
yet.

The basic idea:
Define a model (like IncomeReport) in you schema having the attribute
"export: none". Which stops Doctrine from making any database
representation (tables) for this model. A simple example:

IncomeReport:
tableName: income_reports
columns:
name:
type: string
primary: true
selling_price:
type: float
cost_price:
type: float
count:
type: float
total:
type: float
date:
type: date
time:
type: time
attributes:
export: none

Now, when IncomeReport is a model you can declare it in modules.yml
and then generate admin module by running "sf dmAdmin:generate".

Open the IncomeReportTable class and define a method to retrieve the
entries. A simplified version (derived from my use-case) is the
following:
public function getListQuery( ) {
$q = new Doctrine_RawSql();
$q->select( '{i.*}' )
->from( <<<END_SQL
((
SELECT
bp.name AS name,
bp.price AS selling_price,
bp.cost_price AS cost_price,
COUNT(br.id) AS count,
bp.price*COUNT(br.id) AS total,
br.scheduled_on AS date,
br.scheduled_at AS time
FROM beauty_reservation br
LEFT JOIN beauty_procedure bp ON bp.id=br.procedure_id
GROUP BY name
ORDER BY br.scheduled_on, scheduled_at
)
UNION
(
SELECT
p.name AS name,
p.selling_price AS selling_price,
p.delivery_price AS cost_price,
SUM(po.quantity) AS count,
p.selling_price*COUNT(po.id) AS total,
po.created_at AS date,
'00:00:00' AS time
FROM product_order po
LEFT JOIN product p ON p.id=po.product_id
GROUP BY name
ORDER BY po.created_at
)
ORDER BY date, time) i
END_SQL
)
->addComponent( 'i' , 'IncomeReport' );
return $q;
}

Now you have Doctrine_RawSql query which allows almost all methods of
Doctrine_Query and you end up with Collection!

A small bit of tweaking of Form and Configuration classes is required
to support Doctrine_RawSql. When I'm done with them I'll post them.

On 24 Юни, 11:08, Roel Sint <rmsi...@gmail.com> wrote:
> I have been searching for a similar solution and did not found much
> either. Therefore I experimented by retrieving a list of id's. And
> then hydrate the records individually, because of this result caching
> will be probably needed if it is used in production. The result is
> that you have an array, not a collection :-(, with all different
> objects, because it is a pager the results are nicely sorted. Don't
> know if this helps, perhaps it gives you an idea.
>

> See:https://github.com/beeldspraak/bsdmSocialMediaPlugin/blob/master/lib/...https://github.com/beeldspraak/bsdmSocialMediaPlugin/blob/master/README

[kdizza]

You can create a MySQL view. Then construct your schema as you would
a normal table. You can also include Foreign Keys as well. That way
you use Diem's native search, pagination and filtering without having
to construct a class from scratch.

From there all you need to do is disable all the batch functions and
edit links in Diem.

[antitoxic]

kdizza,
Is there an official method to declare views in Doctrine schema?

The only thing that I found was:
http://stackoverflow.com/questions/1278766/views-in-doctrine-orm

which suggests dropping table and creating a view with the same name
every time the database is rebuild

[antitoxic]

I got it working. Thank you.
But I noticed several usability issues.Should I create a new threads?
- How to automatically define view ?
- If you use the the method described above, there isn't a built-in
way to autoimport sql.