Hi Everyone, I have a query that I have been working. I have tried everything but not been able to make it work. Any suggestions would be great. Here is the plain mysql Query which I want to get using ORM.
Select * from tablename WHERE country='Australia' Group by concat('Email','Name') order by addeddate DESC
Anything added to .Values(‘HERE’) are added on group by
Actual Query
The query returns correct number of results by removing duplicates, however, any field added to select gets added to group by which I dont want. Any working example would be a great help.
My understanding of group by is that the values that you group by are the only fields that can appear in the select clause (along with the aggregation functions).
If you look at the mysql documentation for group by, it echos what I said, but says that mysql allows some extension to it, however, I don’t think you can get the django ORM to produce such queries.
quote from documentation:
In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the nonaggregated name column in the select list does not appear in the GROUP BY :
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.
The query seems to imply that you have cases in the database where the combination of email and name does not identify a single record (otherwise, countPer would always be 1). So, it is possible in such cases for there to be different values for other columns.
To be more concrete: Since you have a “country” column, let’s assume you also have a “city” column. As far as I can see, it is totally possible for your database to contain two records, both having email='john@example.com' and name='John Smith', but one of them showing city='Melbourne' and the other city='Perth'. Now, if you added the city column to the query without grouping on it, what would you expect to see in the record with person='john@example.comJohn Smith'?
Given what you described so far, there is no sensible correct answer. That is why the SQL standard disallows such queries. MySQL allows them, and just chooses a value from one of the relevant records – which is, in general, completely bogus (there is a reason why they do it, but I don’t want to get into this discussion here).
Anyway, the question above is not just rhetorical. Please think about it. Once you have a satisfactory answer, you will probably know how to proceed, and if not, it will be a lot easier to help you.
Hey Shaib,
Really appreciate your answer.
Actually its not the ‘name’ column, Sorry about the query. I just used that as an example because I dont want to confuse by providing all the details. Here is the actual scenario:
We list courses in our site and user enquire these courses. These courses are from different course Universities and colleges, we call them providers. If a user enquire two courses from same provider we still need to count them as one lead. Hence, the column is provider not name, sorry for the confusion.
provider column is a text field with the provider code (eg: XYZ). So, if a user name “John Doe” with email "john.doe@test.com" enquiries two courses for XYZ I want to remove one record from my query. In Plain MySQL query, I was able to do that using Group By concat(provider,email) (john.doe@test.comXYZ) which is always unique. But with ORM I am not able to do that. I want to select all the fields from the table but with unique combination of provider and email, is there any other ways I can do that?
Please let me know if you have any other questions.