Query Builder return null for join result


#1

I want to get count result by Database.raw in a select query of the query builder but when my main table is empty, all fields of my query are null except Database.raw result.

Here is my query:

const jobs = await Database.table('jobs')
  .leftJoin('job_offers', 'jobs.id', 'job_offers.job_id')
  .select([
    Database.raw(`'job' as type`),
    Database.raw('COUNT(jobs.id) as offers_count'),
    'jobs.id', 'title', 'jobs.description', 'jobs.created_at',
  ])
  .limit(limit).offset(offset)

and my query result is:

[
    {
        "type": "job",
        "offers_count": 0,
        "id": null,
        "title": null,
        "description": null,
        "created_at": null
    }
]

In the above query, the result must be [] when there are not any row in my job table.

What is wrong in my query?


#2

Remember the JOIN theory

  • Inner Join matches both tables and shows data linked in two columns (by the usage of Primary Key and Foreign Key)

  • Left Join Shows all records between two tables included records from the left table that are not linked with any record from the right table

  • Right Join Shows all records between two tables included records from the right table that are not linked with any record from the left table

Please make a review of your scenario again choose what join operator do you need

Also your select query must be in this way


.select('tableName.columnOne', 'tableName.columnTwo')
.select(Database.raw("COUNT(tableName.columnName) AS Alias"))


#3

Thanks for your reply @ShadowPaz.

My issue is not about JOINs types. It’s all about select and Database.raw() .
I also tried the way you did mention but it not work and return same result.