0

I would like to "convert" a SQL query in a solr command.

I have 2 SQL tables "jobs" and "companies".

Today to count the number of jobs published by a company, I run this query :

select c.id, count(j.id) 
from companies c
left join jobs j on j.client_id = c.id
group by c.id;

In other hand I have 2 collections "jobs" and "companies" there is the same fields.

How can I "convert" the query below in solr ?

I saw it's possible to make a join with a parent collection, but I don't want to create a hierarchy between job and company (it doesn't make sense).

barden
  • 349
  • 6
  • 18

1 Answers1

0

You can't implement a direct version of the query, as Solr doesn't do joins the way you want to do joins. A possible solution would be to facet on client_id in jobs, so you get a count for each company id, then look up values from that result when you display the counts for each company (if you really need the left join part). If the client_id isn't present in the data, its count is 0. Something like facet=true&facet.field=client_id should work.

If you only need counts for companies present in the index, I'd index the company name together with the id, so that you can facet on the name directly.

MatsLindh
  • 49,529
  • 4
  • 53
  • 84
  • Yes facet may works if I request in "jobs" collection, but I request in "companies" collection that doesn't have any job_id. – barden Sep 27 '16 at 09:47
  • As I describe - you'll have to do two queries if you also want to retrieve companies without jobs. Either by first searching companies and then using the company ids to limit the result set in jobs, or by first searching jobs, and then using that facet to generate the counts for each company in your result set. You could possibly use streaming expressions for this, but that relies heavily on which version of Solr you're on (and if your library supports it). – MatsLindh Sep 27 '16 at 10:46