Query works on MySQL workbench but not on Laravel-CodePudding

I've been sending a mails on Laravel using a body from a database, everything was working just fine until i add a join function in the laravel query. When I'm typing my query on MySQL workbench i have one row returned as expected but not with laravel.

Welcome.php :

<?php

namespace App\Mail;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Mail\Mailable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;

class Welcome extends Mailable
{
    use Queueable, SerializesModels;

     
    /**
     * Create a new message instance.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Build the message.
     *
     * @return $this
     */
    public function build()
    {
        return $this->from('[email protected]', 'John Doe')
            ->subject('Welcome')
            ->markdown('mails.welcome')
            ->with([
                'name' => 'New User',
                'wMail' => DB::table('mails')
                            ->join('users', 'mails.user_id', '=', 'users.id')
                            ->where([
                                ['mails.user_id', 'users.id'],
                                ['mails.name', 'Welcome Email']
                            ])->get(),
            ]);
    }
}

User.php :

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array<string, string>
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    /**
     * Get the mails for a user.
     */
    public function mails()
    {
        return $this->hasMany(Mail::class);
    }
}

Mail.php :

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Mail extends Model
{
    use HasFactory;

    /**
     * Get the user for a mail.
     */
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

2022_03_07_111014_create_mails_table.php :

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('mails', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained();
            $table->string('name');
            $table->string('object');
            $table->text('body');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('mails');
    }
};

I also have 5 users in my database, and one mail corresponding to the following factory :

    'user_id' => 3,
    'name' => 'Welcome Email',
    'object' => 'Bienvenu sur UH-Lawyers',
    'body' => $this -> faker -> text(200)

welcome.blade.php :

@component('mail::message')
Hello {{$name}},

@if (count($wMail)>0)
    @foreach($wMail as $mail)
        {{$mail->body}}
    @endforeach
@else 
    No result 
@endif

@component('mail::button', ['url' => ''])
Button Text
@endcomponent

Thanks,<br>
{{ config('app.name') }}
@endcomponent

So when I type this query on MySQL : SELECT body FROM Mails.mails join Mails.users on users.id = mails.user_id where users.id = mails.user_id and mails.name ="Welcome Email"; I have one row returned corresponding to the mail's body I expect. But when I'm sending my mail on Laravel, "No result" appears on my mail's body. But when I change my Laravel's query from :

...->where([['mails.user_id', 'users.id'],...

to :

...->where([['mails.user_id', '3'],...

everything works just fine!!! I really don't get what the problem is...

CodePudding user response：

In your where clause, the first item filters the column mails.user_id on the value 'users.id' which is a string.

I'm guessing this is not what you intend to do. Assuming you want to filter that column on a specific user ID, you need to supply that actual value.

Usually, you can retrieve the current (logged in) user's ID with Auth::user()->id. So I suggest you update your build() method as follows:

// Welcome.php

public function build()
{
    $currentUserId = Auth::user()->id;

    return $this->from('[email protected]', 'John Doe')
        ->subject('Welcome')
        ->markdown('mails.welcome')
        ->with([
            'name' => 'New User',
            'wMail' => DB::table('mails')
                        ->join('users', 'mails.user_id', '=', 'users.id')
                        ->where('mails.user_id', '=', $currentUserId)
                        ->where('mails.name', '=', 'Welcome Email')
                        ->first(),
        ]);
}

If, however, you wish to pass in another specific user ID, then you'll need to use a constructor argument for that:

// Welcome.php
// (imports, traits and comments removed for brevity)

namespace App\Mail;

class Welcome extends Mailable
{
    // ...

    protected int $userId;


    public function __construct(int $userId)
    {
        $this->userId = $userId;
    }

    public function build()
    {
        return $this->from('[email protected]', 'John Doe')
            ->subject('Welcome')
            ->markdown('mails.welcome')
            ->with([
                'name' => 'New User',
                'wMail' => DB::table('mails')
                    ->join('users', 'mails.user_id', '=', 'users.id')
                    ->where('mails.user_id', '=', $this->userId)
                    ->where('mails.name', '=', 'Welcome Email')
                    ->first(),
            ]);
    }
}

And then, when you create your Welcome mailable instance:

$userId = 0; // TODO: Retrieve the user ID you need
Mail::to('[email protected]')->send(new Welcome($userId));

The relevant documentation can be found on that page: https://laravel.com/docs/9.x/mail#sending-mail

CodePudding user response：

If that query workson MySQL, and you have no way to making it work in laravel with that Syntax, you can try to use Laravel's DB class, with the following syntax:

$awnser = DB::select('SELECT body FROM Mails.mails join Mails.users on users.id = mails.user_id where users.id = mails.user_id and mails.name ="Welcome Email"');

But you should use parameters instead of writing everything there:

$welcome = "Welcome Email";
$awnser = DB::select('SELECT body FROM Mails.mails join Mails.users on users.id = mails.user_id where users.id = mails.user_id and mails.name =:name', ['name' => $welcome]);

This solution may work for you.