function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Сергей Жугин 7Сергей Жугин 7 

SOQL querry

Hi everyone. I work with small project and ran into a problem. I need to display the most popular genre of the song in the mix object and the second after it, how can I do it using the main syntax of SOQL. If you give SOQL code or example it would be cool.
User-added image
Best Answer chosen by Сергей Жугин 7
Alain CabonAlain Cabon
Hi Sergey,

Probably solved now (don't forget to post the solution in this case and close the question), otherwise, for complex problems with SOQL queries, you will have more answers here with a sample of data and the expected result.

The following query is not the solution but just a possible option by using the count of song Ids directly. 
select mix__r.primary_genre__c, count(id), count(song__r.id)
from track__c
group by mix__r.primary_genre__c
order by count(song__r.id) desc
limit 2

The problem is that it is not the result probably that you are looking for.
The SOQL queries are very restrained when you combine many objects and only the existing relationships can be used (you cannot join fields outside the existing relationships).

All Answers

Сергей Жугин 7Сергей Жугин 7
Maybe it can do with aggregate result
Alain CabonAlain Cabon
@Сергей Жугин

It is again better to post some sample data and the expected result.

The following queries that you probably already wrote are not accurate or suitable for what reasons in your case?
  
select mix__r.primary_genre__c, count(id)
from track__c
where song__r.genre__c = 'my selected genre'
group by mix__r.primary_genre__c
order by count(id) desc
limit 2
  
select mix__r.secondary_genre__c, count(id)
from track__c
where song__r.genre__c = 'my selected genre'
group by mix__r.secondary_genre__c
order by count(id) desc
limit 2
  
select mix__r.primary_genre__c, mix__r.secondary_genre__c, count(id)
from track__c
where song__r.genre__c = 'my selected genre'
group by mix__r.primary_genre__c, mix__r.secondary_genre__c
order by count(id) desc
limit 2

We also create rollup summary fields in some cases but it is not interesting here.
 
Сергей Жугин 7Сергей Жугин 7
Hi Alain, thank you for your answer. I try to explain you what i need more correctly. I want to write most common genre in Mix.primary_genre__c and second most common in Mix.secondary_genre__c. And i need to it then i sort out Mix (FROM Mix__c). A little about logic of database Mix Of Song consist of Tracks. Track is junction object between Mix and Sond. 
Сергей Жугин 7Сергей Жугин 7
To find most popular genre we need to count number of Songs of certain genre.
Alain CabonAlain Cabon
Hi Sergey,

Probably solved now (don't forget to post the solution in this case and close the question), otherwise, for complex problems with SOQL queries, you will have more answers here with a sample of data and the expected result.

The following query is not the solution but just a possible option by using the count of song Ids directly. 
select mix__r.primary_genre__c, count(id), count(song__r.id)
from track__c
group by mix__r.primary_genre__c
order by count(song__r.id) desc
limit 2

The problem is that it is not the result probably that you are looking for.
The SOQL queries are very restrained when you combine many objects and only the existing relationships can be used (you cannot join fields outside the existing relationships).
This was selected as the best answer