PropelHello,
I have a question about using behavior in Propel. As I actually understand,
behavior in Propel add extensible capabilities to a Propel object. Take, for
example, sfPropelActAsRatableBehaviorPlugin (
http://www.symfony-project.org/plugins/sfPropelActAsRatableBehaviorPlugin)
which allows you to add the possibility to vote on any object of any type.
In order to accomplish that, the developpers have to ignore the relations
between object. Let's take a look at schema.yml
propel: _attributes: { package:
plugins.sfPropelActAsRatableBehaviorPlugin.lib.model }
sf_ratings: _attributes: { phpName: sfRating, package:
plugins.sfPropelActAsRatableBehaviorPlugin.lib.model } id:
ratable_model: { type: varchar, size: 50, required: true }
ratable_id: { type: integer, required: true } user_id: {
type: integer } rating: { type: integer, required: true,
default: 1 } _indexes: ratable_index: [ratable_model, ratable_id]
ratable_uindex: [ratable_model, ratable_id, user_id]
There are ratable_model and ratable_id in sf_ratings table and they are all
*indexed*. As you can see, there is no foreign key in this table, which is
totaly normal since it has to be so.
Now my problem is that I have a table names Article with about 6000
articles, each article has average 2 votes which results in 12000 records in
sf_ratings table without couting the number of votes on another type of
object such as comments, user... and I would like to select the list of
article order by vote. The query is pretty straightforward:
"SELECT article.ID, article.TITLE, AVG(sf_ratings.rating) as note FROM
article LEFT JOIN sf_ratings ON article.ID = sf_ratings.RATABLE_ID WHERE
sf_ratings.RATABLE_MODEL = 'Article' GROUP BY article.ID ORDER BY note DESC
LIMIT 15".
The query takes about 15 seconds to execute which is not possible in
production environment. When I add a foreign key to sf_ratings table to test
the performance of the query, it takes only ~1 second to finish the query.
So the fact that there is no foreign key in sf_ratings table really kill the
perfomance of your database. On the other hand, if I add the foreign key to
the table, it has no sense since the goal of the plugin is to add rating
possibility to any object of any type.
If any of you have has any idea on how to solve this problem, I would be
really appreciated it.
Thanks.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
