Home / Questions / 530791
Accepted
René Merlo
Asked: 2022-06-29 13:10:16 +0800 CST 2022-06-29 13:10:16 +0800 CST

Query using Eloquent applying a Where on a 1-to-many related table

  • 772

I need to do a query using Eloquent on a couple of tables that are 1-to-many related and I'm not able to figure it out. Here is the code I am using:

$datos = Vigelectrodetenida::query()
    ->with('relUsr')
    ->where('cam1b', 'LIKE', '%' . $this->busquedas . '%')
    ->orWhere('cam1c','LIKE', '%' . $this->busquedas . '%')
    ->orderBy($this->campOrden, $this->direcOrden)
    ->paginate(20);

I use this code to generate a table that has a lookup input using livewire. For now, the queries I make on the cam1b or cam1c field work fine, but when I want to put another orWhere and have it act on the name field that is in the users table (relUsr) it stops working, it tells me that the name field. Next I put the code that I am using and the error that is being generated.

$datos = Vigelectrodetenida::query()
    ->with('relUsr')
    ->where('cam1b', 'LIKE', '%' . $this->busquedas . '%')
    ->orWhere('cam1c','LIKE', '%' . $this->busquedas . '%')
    ->orWhere('name','LIKE', '%' . $this->busquedas . '%')
    ->orderBy($this->campOrden, $this->direcOrden)
    ->paginate(20);

enter image description here

Well, I hope I have been able to convey my doubt correctly and that you can help me.

I want to thank @HeytalePazguato for his valuable help

laravel-8

1 Answers

  1. Best Answer

    Good day,

    One way to do it is with select()andjoin()

    The general way to build it is like this

    $query = Modelo::select('tabla_del_modelo.*')->join('tabla_del_valor_foraneo', 'tabla_del_valor_foraneo.columna', '=', 'tabla_del_modelo.columna')

    Where:

    • Model => The model in which you want to display the information.
    • table_of_the_model => The migration (Table) of the previous model.
    • tabla_del_valor_foraneo => The migration (Table) of the foreign column you wish to add.
    • column => The name of the column within the corresponding migration (Table).

    Using the data you put in your question and comments (As you did not add your migrations or your models you may have to adapt them)

    $datos = Vigelectrodetenida::select('si05_vigelec_dtosdetenida.*')->join('users_dnpc', 'users_dnpc.id', '=', 'si05_vigelec_dtosdetenida.id_usr')

    After that you can already add your where(), and all the fields that are from the table users_dnpcwill have to be declared through the table name, for example:

    ->orWhere('users_dnpc.name','LIKE', '%' . $this->busquedas . '%')

    Note: If there is any column that has the same name in both tables (For example, in case you use created_atand updated_at) you will have to declare them through the table name, that is, users_dnpc.created_atandsi05_vigelec_dtosdetenida.created_at

    • 1