[SOLVED] Using getCount() with innerJoin() or leftJoin() returns 1 instead of the actual total..

Hi all,

I have some weird issue going on with the $modx->getCount() function. I use this inside my components processor to tell Ext how many results there are to let him create enough pages. But for some reason when I use innerJoin (or leftJoin) in my query the total is always set to 1 instead of the actual total (I expect to have 3500+ records).

When I run the query directly in my database I will get the results. Either the processor haves the 20 first results (because I have a LIMIT 0,20) but only the total number of rows are not good.

Does anyone know how this works? Thanks in advance! [ed. note: bertoost last edited this post 14 years, 11 months ago.]

http://rtfm.modx.com/display/xPDO20/xPDO.getCount
The limit method should be called after the getCount. Maybe that's the problem?
Or you can use PHP's count function to count the elements of your collection.

You should provide the code for reference...

I have relations and each relation has many addresses (of sereval types). These relations are defined in the xml schema and selecting it with getObject and getMany works fine! Below the example of selecting all within one query (to do some search)

Here the code examples;



In this situation the getCount always returns 1

First, you can't use groupby with limit/offset, nor is that a legal groupby for most database engines or for working with objects via getObject/getCollection/etc. Apply any grouping, sorting, limiting, etc. after the getCount().

Hmm okay, but when I run the query directly in the database it works fine! So this isn't a query issue...

And beside that; when I do the groupby after the getCount() I count 4 records of one relation, because the relation has 4 addresses (for example). And then the result total isn't right also because after the groupby there are less results

Solved thanks to Jeroen Kenters.. I'm now doing a select DISTINCT on the Relation.id field and leave the groupby out! That works great!