Query in related tables

Question

How would be an example of a bd query with related tables and an insertion of data in them.

I have the tables :

Produtos  
Tamanhos   
Generos

I need to display in the view produtos.blade.php a list of all registered products and also their fields tamanho and genero that are identified by their id’s in the table produtos.

EDIT:

I posted my models code view and route here: http://pastebin.com/MpqvxZay

there are the errors that are returning

//model Produto
<?php
class Produto extends Eloquent
{
        // Produtos has_many Tamanhos
        public function tamanhos()
        {
                return $this->hasMany('Tamanho');
        }
}

//model tamanho
<?php
class Tamanho extends Eloquent
{
        public $timestamps = false;

        // Tamanhoss belongs_to Produtos
        public function produtos()
        {
                return $this->belongsTo('Produto');
        }
}


//route com o eloquent
Route::get('/teste', function()
{
        $produtos = Produto::all();
        return View::make('produto.teste', compact("produtos"));
});


//foreach da view
 @foreach($produtos as $produto)
                <tr>
                    <td><input type="text" value="{{ $produto->tamanho->descricao }}" name="title" /></td>
                </tr>
 @endforeach


//Erro exibido na tela
Trying to get property of non-object

/* se eu mudo no foreach da view $produto->tamanho->descricao por $produto->tamanhos->descricao
me dá esse erro:
*/
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tamanhos.produto_id' in 'where clause' (SQL: select * from `tamanhos` where `tamanhos`.`produto_id` = ?) (Bindings: array ( 0 => 1, ))



sendo que na tabela produtos tem o campo tamanho_id para servir como chave estrangeira com a tabela tamanhos

Answer 1

If you are using the Eloquent, just define the relations in the model produtos and Laravel does the magic for you.

In your product model, use the following link code:

public function tamanho()
{
     //Aqui deve ser posto o nome do model para a tabela tamanho.
     return $this->belongsTo('tamanho'); 
}

... the same should be done for generos.

Once done, simply iterate over the list of products (with a foreach, for example) and directly access information from related tables as if they were attributes of the object produto.

$produto->tamanho->valor

...for example, is a way to access the field valor, contained in the table tamanho through the object produto.

Eloquent is simply one of the most beautiful things I’ve ever seen in the PHP world. ;D

... hope it helps!

EDITE 1:

If yours produtos has a foreign key to tamanhos, then the relationship is not hasMany and yes belongsTo.

Answer 2

You see, Henry... I answered another question of yours today... but I’ve only just realized a few things... you’re not defining relationships properly. We have to "go back a little" and think about it:

1º - You have a schematic in the database, right? As far as I have seen, in your table "products" you have a field "size_id". In this case, this is defining a relationship one-to-Many ("one for many"), where ONE SIZE can be assigned to MANY PRODUCTS (and simultaneously Many-to-one, where several products may have a single size).

2º - In your model, you define a property "sizes", and a relationship hasMany. That’s wrong. That would only be right if A PRODUCT COULD HAVE SEVERAL SIZES. The right way to put the relationship you want, in the models, is the following:

//model Produto
<?php
class Produto extends Eloquent
{
    // Produto belongs to Tamanho
    public function tamanho()
    {
        return $this->belongsTo('Tamanho');
    }
}

//model tamanho
<?php
class Tamanho extends Eloquent
{
    public $timestamps = false;

    // Tamanho has many Produtos
    public function produtos()
    {
        return $this->hasMany('Produto');
    }
}

Once the templates are corrected and consistent with the database schema, you can use the method with to prevent a new "SELECT" from running every interaction in the LOOP that will display the products in the view. For example:

//route com o eloquent
Route::get('/teste', function()
{
    $produtos = Produto::with('tamanho', 'genero')->get();
    return View::make('produto.teste', compact("produtos"));
});

Finally, the view will work the way it is:

@foreach($produtos as $produto)
    <tr>
        <td><input type="text" value="{{ $produto->tamanho->descricao }}" name="title" /></td>
    </tr>
@endforeach