CODEWITHSUNDEEP
phppostgresqlpropel
Sinjuice
Sinjuice

Reputation: 562

Propel is not returning all the rows

I'm trying to execute a query using propel and it should return 50 rows of data, but instead of that it returns other random number of rows. I printed the query copied in my pgphpadmin and it returns the 50 rows. I'm not sure what is happening. Also if I execute "count" function before "find" it returns 50, but after it returns the random amount of rows. 

$limit = 50;
$offset = 0;
..... //filters 
$companies = $companies->orderById()->limit($limit)->offset($offset);
var_dump($companies->count()); // this returns 50
$companies = $companies->find();
var_dump($companies->count()); // this returns 13

Also the foreach after iterates only 13 times.

And this is the query generated

SELECT "company"."id", "company"."otherfields",
"lists"."id", "lists"."otherfields", "place"."id",
"place"."otherfields", "contact"."id", "contact"."otherfields", 
"entry"."id","entry"."otherfields" 
FROM "company" LEFT JOIN "lists" ON 
("company"."sector_id"="lists"."id") LEFT JOIN "place" ON 
("company"."country_id"="place"."id") LEFT JOIN "contact" ON  
("company"."id"="contact"."company_id" AND "contact"."active" = true)
LEFT JOIN "entry" ON ("company"."id"="entry"."company_id") 
WHERE "company"."active"=true ORDER BY "company"."id" ASC LIMIT 50 OFFSET 0

The Propel version is 2.0-dev

I always had a problem with this query so I think that the problem may come from the fact that I removed one throw in the library. I'm using with() and limit(). The query generated is ok, but the result is not.

Changed Propel/Runtime/Formatter/ObjectFormatter.php I've commented this.

/*if ($this->hasLimit) {
            throw new LogicException('Cannot use limit() in conjunction with with() on a one-to-many relationship. Please remove the with() call, or the limit() call.');
}*/

I imagine that the error might come from here.

My join query looks like this

$companies->leftJoinWithLists()->leftJoinWithPlace()->leftJoinWithContact()->addJoinCondition('Contact','Contact.active = ?', true)->leftJoinWithEntry();

And then I add the limit. Any way to avoid this or do it other way?

Upvotes: 0

Views: 654

Answers (1)

Ben
Ben

Reputation: 713

Any way to avoid this or do it another way?

You need to do it a different way. See my comment on the issue you created at https://github.com/propelorm/Propel2/issues/1231

Basically, the LIMIT clause in SQL will limit the number of rows in the result set, but since you are using a LEFT JOIN, one company entity may be represented in multiple rows of the result set (see the raw result in pgphpadmin).

The solution: Use multiple queries. I suggest querying first for all the companies you want, then using the ->populateRelation() method on the resulting ObjectCollection.

Upvotes: 1

Related Questions