Smalltalk › Frameworks & Tools › GLORP

Queries, one-to-many mappings and sqlCount

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

2 messages Options

Tommaso DS

Queries, one-to-many mappings and sqlCount

Hello,

I am using Glorp on Pharo 3. So far so good (I have to admit it is kind of fun), but there is a thing I cannot understand.

I have a basic relationship between two objects (tables): User and Posts, that I mapped with one-to-many.
I am trying to execute a query to determine the number of posts of each user without loading all the posts in the image, but I get some errors and I don't know what am I doing wrong.

The query looks like:

query := (Query readManyOf: User)
retrieve: [ :eachUser | eachUser posts sqlCount ];
yourself.

But if I run it I get a MNU: "receiver of isRelationship is nil" at the moment of creating the query.
I also tried to use count instead of sqlCount: In this case I get a "GlorpDatabaseReadError: ERROR: count(*) must be used to call a parameterless aggregate function" when I execute the query.

Is there a proper way to do this without loading all the Posts in memory? Should I write an explicit Join?

Thanks!
Tommaso

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

Alan Knight-2

Re: Queries, one-to-many mappings and sqlCount

I think there are a couple of possible problems. I don't have code handy at the moment but
- I think you also need to retrieve something about the user to distinguish the row, so, e.g. retrieve: [:eachUser | eachUser id];

- aggregate functions are awful to figure out. I usually try to find an example that looks similar in the tests to remind myself, or I look for the old blog posts I wrote at the time. And I wrote the code. I *think* that count might work if you did it on an attribute of the posts and did a group by. So, e.g. eachUser posts id count, and a separate groupedBy: [:eachUser | eachUser id]. But if I'm honest, I kind of forget the distinction between sqlCount and count, so maybe it's sqlCount that would work that way.

- There's a deep problem in Glorp that sometimes it needs to know information about the query before it's available. Or, looking at it the other way, that it needs to be able to postpone figuring out certain things. So when you create a query like that it doesn't have the session, which means it doesn't actually have the baseExpression that the query is built on, and thus the descriptor. It kinds of sounds like that's the issue when I see isRelationship sent to nil. It's asking if some mapping (e.g. #posts) is a direct attribute reference, or if it's a relationship, and it doesn't know yet because it doesn't have the descriptor. Working around this is ugly, and I forget exactly what I've done in the past. It may be enough to ask the session for the query instead of creating it directly. Or you might need to force the baseExpression to be one that has a descriptor. Maybe Niall, or someone else who's been working with the code more recently has a better answer.

- I'm not sure what version the current Pharo code is based on. There might be an old bug.

On Thu Feb 05 2015 at 8:43:26 AM <[hidden email]> wrote:

Hello,

I am using Glorp on Pharo 3. So far so good (I have to admit it is kind of fun), but there is a thing I cannot understand.

I have a basic relationship between two objects (tables): User and Posts, that I mapped with one-to-many.
I am trying to execute a query to determine the number of posts of each user without loading all the posts in the image, but I get some errors and I don't know what am I doing wrong.

The query looks like:

query := (Query readManyOf: User)
retrieve: [ :eachUser | eachUser posts sqlCount ];
yourself.

But if I run it I get a MNU: "receiver of isRelationship is nil" at the moment of creating the query.
I also tried to use count instead of sqlCount: In this case I get a "GlorpDatabaseReadError: ERROR: count(*) must be used to call a parameterless aggregate function" when I execute the query.

Is there a proper way to do this without loading all the Posts in memory? Should I write an explicit Join?

Thanks!
Tommaso

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.