How i can make this query using lucid models/query builder in adonis?

Question

I need to make a query that return all book_unit_questions of one Book.

So, i have the Book.Id.

I'm trying something like:

SELECT BO.id,
BUQ.description
FROM book_unit_question BUQ 
JOIN book_unit BU
ON(BUQ.book_unit_id = BU.book_id)
INNER JOIN Books BO
ON(BU.book_id = 1)

But this way is returning id from other books, and i was expected some the id 1:

My migrations files:

class BookSchema extends Schema {
  up () {
    this.create('books', (table) => {
      table.increments()
      table.string('code').notNullable().unique()
      table.string('description')
      table.string('authors')
      table.boolean('status').defaultTo(false)
      table.integer('user_id').references('id').inTable('users')
      table.timestamps()
    })
  }

class BookUnitSchema extends Schema {
  up () {
    this.create('book_unit', (table) => {
      table.increments()
      table.integer('book_id').references('id').inTable('books').notNullable()
      table.integer('unit').notNullable()
      table.integer('sequence').notNullable()
      table.string('description')
      table.integer('qt_question')
      table.boolean('status').defaultTo(false)
      table.integer('user_id').references('id').inTable('users')
      table.timestamps()
      table.unique(['unit', 'sequence', 'book_id'])
    })
  }

class BookUnitQuestionSchema extends Schema {
  up () {
    this.create('book_unit_question', (table) => {
      table.increments()
      table.integer('book_unit_id').references('id').inTable('book_unit')
      table.string('question_form')
      table.string('option_form')
      table.string('type_answer')
      table.string('description')
      table.string('correct_answer_description')
      table.integer('correct_answer_description_id')
      table.text('image_sound')
      table.boolean('status').defaultTo(false)
      table.integer('user_id').references('id').inTable('users')
      table.timestamps()
    })
  }

crbast · Accepted Answer · 2019-11-15T11:53:14.050

1

Edited : This version does not use `manyThrough()`

Controller :

    const BookUnit = use('App/Models/BookUnit')
    const Question = use('App/Models/BookUnitQuestion')

    const bIds = await BookUnit.query().where('book_id', 1).ids() //Get all bookUnit ids

    const questions = await Question.query().whereIn('book_unit_id', bIds).fetch()

    return questions

Paginate :

const questions = await Question.query().whereIn('book_unit_id', bIds).paginate(2, 2)

Migration files :

this.create('books', (table) => {
      table.increments()
      table.string('code').notNullable()
      table.string('description').notNullable()
      table.timestamps()
})

this.create('book_units', (table) => {
      table.increments()
      table.integer('book_id').unsigned().references('id').inTable('books')
      table.integer('unit')
      table.timestamps()
})

this.create('book_unit_questions', (table) => {
      table.increments()
      table.integer('book_unit_id').unsigned().references('id').inTable('book_units')
      table.integer('description')
      table.timestamps()
})

Don't hesitate if you have any questions

edited Nov 15 '19 at 11:53

answered Nov 13 '19 at 09:14

crbast

2,192
1
11
21

1

select count(*) as "total" from "book_unit_question" where "book_id" = $1 - column "book_id" does not exist – bla Nov 13 '19 at 16:44
I didn't use the same table naming convention. Please read this documentation https://adonisjs.com/docs/4.1/relationships#_has_many to define column name – crbast Nov 13 '19 at 16:51
1

the problem in in manyThrough right? i try: book_unit_questions() { return this.manyThrough('App/Models/BookUnit', 'book_unit_questions', 'id', 'id') } but i get the same error – bla Nov 13 '19 at 17:05
try this: manyThrough('App/Models/BookUnit', book_unit_questions, 'id', 'Book_id') & hasMany('App/Models/BookUnitQuestion', 'id', 'Book_Unit_Id') – crbast Nov 13 '19 at 17:12
1

can you please share your er diagram? Or give a dump? – crbast Nov 13 '19 at 17:17
1

Okay thx, I'll try it with that later. Currently I can't do it – crbast Nov 13 '19 at 17:23
1

You code is correctly. I'm putting the code in index method. when i put in show() everything works well – bla Nov 13 '19 at 17:42
1

there's a way to apply paginate in this query? – bla Nov 13 '19 at 17:55
Glad to hear it works. After some research I didn't find for `paginate`. Sorry. I advise you to ask the question on the official AdonisJS forum (https://forum.adonisjs.com/) – crbast Nov 13 '19 at 21:29
@veroneseWithS Hi, I changed my answer with a better solution (in my opinion). Tell me if this version better matches your request. I include `paginate()` – crbast Nov 15 '19 at 11:54
1

thanks, i find other way to make this, but thanks anyway, you will continue with the correct answer – bla Nov 15 '19 at 12:00

score 1 · Answer 2 · answered Nov 15 '19 at 11:59

After some tentatives, this code works for my purpose:

 async show(request){
        const bookQuestions = await BookUnitQuestion
                                    .query()
                                    .with('book_unit')
                                    .with('user')
                                    .with('book', (builder) => {
                                        builder.where('id', request.params.id)
                                    })
                                    .paginate()

    return bookQuestions

}

How i can make this query using lucid models/query builder in adonis?

2 Answers2

Edited : This version does not use manyThrough()

Paginate :

Edited : This version does not use `manyThrough()`