Hello everyone. I want to create a query with a sub query and I cannot get it done. Model: I have a...

# exposed

[Goetz Markgraf]

Hello everyone. I want to create a query with a sub query and I cannot get it done.

Model: I have a Table job and a table part where every job has one or more parts.

Requirement: I want to query data from a specific part – and I also want to know how many parts are there for this job.
The result should look something like this:

jobId, partId, some_data_from_this_part, count(parts_for_this_job)
2736, 1, 'data', 2

I have found this stakeoverflow post that looked very much like what we want to do. However, it uses a SubQueryExpression that does not seem to be present anymore.

The documentation for exposed (https://github.com/JetBrains/Exposed/wiki/DSL) is also very short for this topic.

Any help would be appreciated.

[Arjan van Wieringen]

I am confused. You want to get a part with associated jobs, or a job with associated parts? Because your example is a job and a part and info about all the parts ik the job?

For all jobs, you want to get all parts, with their part info and you want to have also a count of all the parts in that job.

[Goetz Markgraf]

I have a job with associated parts. And i want to load some data from one specific task – and additionally the count of all parts from this job.

[Arjan van Wieringen]

What is a task?

[Goetz Markgraf]

Sorry, i meant part

[Goetz Markgraf]

I want to load data from one specific part and the number of parts in this job. There is one-to-many from job to part

[Arjan van Wieringen]

So basically just a join from parts to job and a count grouped by job id

[Goetz Markgraf]

To get the normale data from a part, I just have to where jobid (FK to job) and partId to get the data. For the count , i wanted some kind of sub query the is like SELECT count(partid) from part where jobid = <some job id>

[Goetz Markgraf]

So one select where one column is actually the result of another query, therefore a sub query

[Arjan van Wieringen]

SELECT job.id, part.id, part.info, COUNT(job.id) FROM job JOIN part ON part.jobId = job.id WHERE part.id = …. GROUP BY job.id ?

[Arjan van Wieringen]

You don’t need subqueries for that

[Arjan van Wieringen]

Oh no wait 😁 now the count is too low

[Goetz Markgraf]

Let me do some example. We regard only the table part because it is enough.

We have these parts:

jobid | partid | part_data
------|--------|-----------
123   | 1      | data1
123   | 2      | data2
123   | 3      | data3

Now I need a query that, if I insert the jobid 123 and partid 2 return:

123   | 2      | data2    | 3

And the last 3 is the number of all parts for jobid 123

[Goetz Markgraf]

I have just inserted the question into chatGPT and it came with this SQL statement.

SELECT t1.jobid, t1.partid, t1.part_data, COUNT(t2.partid) AS part_count
FROM your_table t1
LEFT JOIN your_table t2 ON t1.jobid = t2.jobid
WHERE t1.jobid = 123 AND t1.partid = 2
GROUP BY t1.jobid, t1.partid, t1.part_data;

It really seems to work without a subquery. I have never tried to join one table to itself.

Does that work? How do I write this in exposed?

[Arjan van Wieringen]

This query is wrong. You count the part ids

[Goetz Markgraf]

Yes, that would work. I just want to know how many part entries are there with the same jobid

[Goetz Markgraf]

I just verified the query, it works. I will try to build it in exposed tomorrow. A left join should not be a problem.

@Arjan van Wieringen, thank you very much for your time and your help. You were on the right track, I don’t need a sub query for this. Have a good evening – it that fits your time zone.