Help needed with relationship logic between three tables

i dont at all follow why you need from and to, surely each product has one or more translations. why does the direction matter?

Well let's assume I'm going to have a translator who is fluent in both German and English. This translator is going to specify in the app that he wants to translate products from the supplier "Adidas" (id:1) from English (language_id:1) to German (language_id:2). This creates the relationship as described above.

English translation for this supplier already exists (it wouldn't make much sense to create the translation in the first place), but there are no records for German language in the product_list_translation table, where are all the translations stored.

So, when the translator opens the translation relationship he just created, I want to fetch all the data for both languages and display it in a table, in a way similar to this:

SKU | Input language (English) | Output language (German)

EC-050 | Shirt | Not yet translated

The SKU comes from the table product_list (model Product) where you can find general information about products (sku, weight, price), and the input/output language is fetched from product_list_translation where you can find the name, description etc...

Of course, if the product is translated, then the output language is going to display the translated name of the product.

I hope it makes more sense now? Is there a better way to handle this?

Thank you for taking the time to reply.

so I suppose you have a table containing the languages.

The user is shown dropdown and chooses 'english' as from and 'german' as to

In the controller, you receive the product_id to be translated, the from_id and the to_id

So then you just want product and the two translations if they are available.

This is the 'long form' way

$product = Product::find($product_id);

$from  = ProductListTranslation::where('product_id',$product_id)
        ->where('language_id',$from_id)
        ->first();

$to  = ProductListTranslation::where('product_id',$product_id)
        ->where('language_id',$to_id)
        ->first();

I would probably start with this and then refactor to make it a little cleaner by using scopes.

Yes I do have table with languages and I did implement it exactly the same way as you described, except there's one more dropdown where you select which suppliers' products you want to translate.

The code you provided will work for just one product, right?

I want to display all the products in the table so I had something like this in mind (pseudo code incoming):

//relationship controller
public function showAllProducts($relationship_id)
{
    //we need to get the relationship ID first
    $relationship = ProductTranslationRelationship::find($relationship_id)->first();

    //get all the products from the supplier which the translator wishes to translate
    $products = Product:where([['supplier_id', $relationship->supplier_id]])->paginate(15);
  
    //And now I need to somehow get all the translation data for both the languages inside the $products variable but I have no idea how? maybe something like this?
     $products->translations->where([
    ['language_id'=>$relationship->from], 
    ['language_id'=>$relationship->to]
]);

    return view('relationships.show')->with('data', $products);
}

//and in the relationships.show view i want to do this
<table>
<tr>
<th>SKU</th>
<th>Product name (source)</th>
<th>Product name (output)</th>
<th>Action</th>
</tr>

@foreach($products as $product)
    <tr>
        <td>{{$product->sku}}</td>
        <td>{{$product->from->name}}</td>
         @if(isset($product->to->name))
         <td>{{$product->to->name}}</td>
         @else
         <td>Not yet translated</td>
         @endif 
         <td><a href='product/translate/$product->id'>Open</a></td>
     </tr>
@endforeach

</table>

Just to give an update if anyone finds this via search or google - I found a solution but I don't think it's a good way of handling this problem, I ended up doing this in the controller:

public function show($relationship_id)
{
    $relationship = TranslationRelationship::find($id);  
    $data = Product::where('supplier_id','=', $relationship->supplier_id)->paginate(50);        
    
     //Loop through every product there is and find corresponding translation according 
    //to the language_id from the translation relationship
    foreach($data as $product)
    {          
        $from = ProductListTranslation::where([
        ['product_id','=',$product->product_id],
        ['language_id','=',$relationship->from]])
        ->get();
        $product['from'] = $from;
            
        $to = ProductListTranslation::where([
        ['product_id','=',$product->product_id],
        ['language_id','=',$relationship->to]
         ])->get();
        $product['to'] = $to;            
    }
}

And in the view I call it like this:

        <table class="table table-striped">
            <thead>
            <tr>
                <th>SKU</th>
                <th>Input language</th>
                <th>Output language</th>
                <th>Action</th>
            </tr>
            </thead>
            <tbody>
            @foreach($data as $product)
            <tr>
                <td>{{$product->sku}}</td>
                @if(isset($product->from[0]->name))
                    <td>{{$product->from[0]->name}}</td>
                @else
                    <td>Not yet translated</td>
                @endif
                @if(isset($product->to[0]->name))
                    <td>{{$product->to[0]->name}}</td>
                @else
                    <td>Not yet translated</td>
                @endif
                <td><a href="{{route('translations.index')}}" class="btn btn-primary">Translate</a></td>
            </tr>
            @endforeach            
            </tbody>
        </table>