On Wed, Dec 7, 2011 at 7:54 AM, wgis <alpha.sh...@gmail.com> wrote: > First, thank you both for your answers. I'm now realizing that doing > this with the ORM is not so easy as I thought it would be to others > django fellows (it's my first django project, but I'm nailing it =P). > I saw your RAW SQL solution and it looked genius-simple. I have short > experience with the JOIN operation but that make sense to me. Yet, I > tested, and I still get > (Carrot, Flavour, 2.0) > and just > (Cars, Smell, 4.2) (Cars, Usability, 4.9)without the other contexts. > If I remove the WHEREs (not filtering by user or thing), it still > presents without nulls (or 0.0) > Is there other way you can see? > Again, thanks > On 6 Dez, 21:48, Ian Clelland <clell...@gmail.com> wrote: > > On Tue, Dec 6, 2011 at 1:35 PM, Reinout van Rees <rein...@vanrees.org > >wrote: > > > > > Ah! Now I get your point. You also want the "empty" results for which > > > there's no SQL data. Sorry, but I don't see a way in which you can do > that > > > with an SQL query (and so also not with a Django query). > > > > > In case you want all contexts, you'll have to query for those > > > specifically. And afterwards grab the results belonging to that > context. So > > > you won't escape a for loop and some manual work, I'm afraid. > > > > Raw SQL: > > select thing, name, vote from mydatabase_votecontext left join > > mydatabase_vote on (mydatabase_vote.context_id = > mydatabase_votecontext.id) > > where thing='Carrot' and user='Me' > > > > That should return null if there is no vote. If you'd rather have zeros, > > then use this: > > > > select thing, name, ifnull(vote, 0.0) from mydatabase_votecontext left > join > > mydatabase_vote on (mydatabase_vote.context_id = > mydatabase_votecontext.id) > > where thing='Carrot' and user='Me' > > > > There still might be a way to do it with the ORM; but you can definitely > > use raw sql for this. > > > > -- > > Regards, > > Ian Clelland > > <clell...@gmail.com> > > You're right -- it's the 'and user='Me' bit that is messing it up. I know you left the id columns out to simplify the problem, but without a full table structure, it's harder for me to visualize the join and the rows that are getting selected.
Try this: select thing, name, vote from mydatabase_votecontext left join mydatabase_vote on (mydatabase_vote.context_id = mydatabase_votecontext.id and thing='Carrots' and user='me') With that statement, the left join should produce: (votecontext.id, name, vote.id, thing, context_id, user, vote) (1, Flavour, 4, Carrots, 1, Me, 3.0) (2, Smell, NULL, NULL, NULL, NULL) (3, Usability, NULL, NULL, NULL, NULL) (4, Size, NULL, NULL, NULL, NULL) And the select columns should reduce the width, to this: (thing, name, vote) (Carrots, Flavour, 3.0) (NULL, Smell, NULL) (NULL, Usability, NULL) (NULL, Size, NULL) If you absolutely need 'Carrots' in the first column (and you might not; you should have it in the template context anyway, when you need to display it), then you would have to duplicate it in the query, and use another ifnull, like this: select ifnull(thing, 'Carrots'), name, vote from mydatabase_votecontext left join mydatabase_vote on (mydatabase_vote.context_id = mydatabase_votecontext.id and thing='Carrots' and user='me') (Or try Tom's secret sauce, and see if that works :) ) -- Regards, Ian Clelland <clell...@gmail.com> -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
