Query with count

const query = await Database.select(

      "places.category_id",

      "places.name",

      "places.address",

      "places.city_id",

      "places.id",

      "cities.name as city_name",

      "cities.country_code"

    )

      .from("places")

      .innerJoin("cities", function () {

        this.on("places.city_id", "cities.id");

      })

      .limit(length)

      .offset(parseInt(start));
  {
    category_id: 2,
    name: 'place5',
    address: 'place5 address',
    city_id: '20',
    id: 5,
    city_name: 'Paris',
    country_code: 'FR',
   countFromOtherTable: countValue  <---- I want to achieve something like this
  }

how do I add some count from other table?

1 Like

Try this: (Note, it is not tested)

const query = await Database.select(

      "places.category_id",

      "places.name",

      "places.address",

      "places.city_id",

      "places.id",

      "cities.name as city_name",

      "cities.country_code",

      Database.raw('count(*) AS table_count FROM table WHERE table.field = 9')

    )

      .from("places")

      .innerJoin("cities", function () {

        this.on("places.city_id", "cities.id");

      })

      .limit(length)

      .offset(parseInt(start));
1 Like