Before I joined the company they were using an outsourcing team that developed the most of the project. The login used to be VERY slow and the engineer hired before of me found that the "find"s on the user at login time, were performing a few hundred of queries. So he decide to go around using pure SQL.
I went through some of the source code of CakePHP but I admit that I did not touch the ORM part at all. I am looking to our code right now. We have a lot of queries that have to (left and inner) join 10+ tables ( I can see a few with 11 and one with 12). There are a few problems about using CakePHP ORM:
1) You hide to the developer the tables that you are going to use. First of all in these case it is not so clear to know what the recursive number is going to be. You still need to have the database schema in front of you.
2) It does not seem that the ORM give you a lot of control on how a query is resolved.
3) CakePHP does not seem to do just one query but a few of them increasing the time to get a result to unacceptable levels. The user table for example is linked with at least 30 tables (probably more). Some of these relationships are one to many. Some of the queries need to go 3-4 levels deeper. It is absolute madness to think that is Ok having cake making 200+ queries when you can get the result with one query. I read some complains in some articles that were criticizing CakePHP's ORM comparing it to the Ruby's one (that is still not the fastest). Maybe things can be done better. If you have an application serving 200 users that are not too demanding maybe it is Ok running 200+ queries in a find in a controller and having the users sometimes wait for 10+ seconds to the system to respond. But when the number of users increase to 10K+ and 100K+ nobody would be able to use the system. Furthermore to deliver a good user experience, you usually need to give an answer to the user in <less than 200ms and with a SQL query well designed you can do that.
So in the end to have more control on how the data is accessed and keep and acceptable speed we decided to use find only when it needs only one (rarely two) tables and they have maximum one relationship with other table.
I would really like to use the ORM, and I was a strong support of using the ORM for everything but in practice it does not seem to scale well.
Am I missing something here ?
Best,
Chris
On Tue, Nov 1, 2011 at 2:43 AM, WebbedIT <paul@webbedit.co.uk> wrote:
@Chris: Please no, not another person advocating the use of $this-
>query()! Although this one is really special as anytime you join two
models you give up and revert to running your own SQL?!?
Just because you can't get your head around associating models
together and running Model::find (should get your head around
containable too) does not mean you should recommend to others early in
the learning curve to follow your cop-out path.
There are so many reasons to learn how to use Cake properly and it
certainly does not make life harder, in fact you could not be further
from the truth. OK there are some circumstances where you need to
learn how to leverage Model::find a little, but these are very few and
far between and the benefits of having your data arrays passed back in
a manner that the Form helper and Model::save expects them in saves
you a ton of time.
Please go back to the book and take the time to learn it and don't
advise others to give up on it.
Paul.
On Oct 31, 9:07 am, Chris Cinelli
<chris.cine...@formativelearning.com> wrote:
> We have complicated queries that span across 8 tables. We decided to use
> pure SQL code. We defined our data source and run SQL through it defining
> our custom methods in the model that execute the query. If the query is
> just on one table we use the ORM but pure SQL pretty much in the other
> cases. The purpouse of the ORM layer is to make things easier. If you make
> it more complicated, why are you doing it?
> On Oct 31, 2011 1:55 AM, "WebbedIT" <p...@webbedit.co.uk> wrote:> >http://nuts-and-bolts-of-cakephp.com/2008/08/06/habtm-and-join-tricke...
>
>
>
>
>
>
>
> > Without seeing any of your code (model associations would have been
> > nice) I logically assume you are using Article HABTM Tag.
>
> > If so this does not create a model for the join table, but instead
> > puts in place some automagic to allow you to link many-to-many records
> > between the two models. As such you can't run a find on the join
> > model, which is what you would have to do to be able to have
> > conditions spanning the three tables.
>
> > Your options are to use unbind and bind to force joins that you can
> > use:
>
> > Check out the new CakePHP Questions sitehttp://ask.cakephp.organd help>
> > Or, my preferred option is to create a model for join tables
>
> > Article hasMany ArticleTag
> > Tag hasMany ArticleTag
> > ArticleTag belongsTo Article, Tag
>
> > This allows you to easily add extra fields into your join table and to
> > run $this->Article->ArticleTag->find('all', array('conditions'=>array(
> > 'Article.is_read'=>0,
> > 'Article.status'=>1,
> > 'Tag.name'=>'cakephp'
> > )));
>
> > HTH, Paul.
>
> > P.S. Notice how I have changed some of your field names and values.
> > Your isRead field should be lowercased and underscored and if boolen
> > use tinyint(1) with 0 and 1. When searching for articles by tag you
> > would normally recieve the tagname in the request (much better for
> > seo) so to query by Tag.id would require an extra find that I would
> > say is unneccessary when you can filter by Tag.name (especially if you
> > have the name field unique indexed). I would also switch
> > Article.status for numerical values, ideally with a lookup table so
> > you can easily add/edit status text across all articles.
>
> > On Oct 30, 8:50 am, SERKAN TURAN <serkantu...@gmail.com> wrote:
> > > Hi,
> > > I have an query and I could not figure it out what is the cakephp form of
> > > it.
>
> > > query:
> > > -------------------------------------------
> > > SELECT *
> > > FROM
> > > tags`
> > > INNER JOIN `tags_articles` ON (`tags`.`id` = `tags_articles`.`tag_id`)
> > > INNER JOIN `articles` ON (`tags_articles`.`article_id` =
> > `articles`.`id`)
> > > WHERE
> > > `articles`.`isRead` = 'Not' AND
> > > `tags`.`id` = 3 AND
> > > `articles`.`status` = 'New'
> > > ORDER BY
> > > `articles`.`name` DESC
>
> > ---------------------------------------------------------------------------
> > -------------------
>
> > > Thnks..
> > > ST
>
> > --
> > Our newest site for the community: CakePHP Video Tutorials
> >http://tv.cakephp.org
> > others with their CakePHP related questions.
>
> > To unsubscribe from this group, send email to
> > cake-php+unsubscribe@googlegroups.com For more options, visit this group
> > athttp://groups.google.com/group/cake-php
--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
No comments:
Post a Comment