Filter data from DB.

Published 2 months ago by ziben69

Hello guys,

my question is about laravel, eloquent filtering. I have table which have got records: id, firstname, lastname, email.

I know how to display all informations from table, but how can I modify it to this:

Display all e-mails that don't contain firstname and lastname. Something like this:

Table: personal_data firstname lastname email john doe [email protected] dennis rat [email protected] mia donut [email protected]

and i need to have result:

Result: [email protected]

rest of data to the trash. Can someone help me?

public function index(MailRepository $mailRepo)
    {
        $mails = $mailRepo->getAll();
        return view('mails.list', [
            "mailList" => $mails
        ]);
    }~~~

~~~MailRepository:
protected $model;

    public function getAll($columns = array('*'))
    {
        return $this->model->get($columns);
    }~~~

~~~ Mail model:
protected $fillable = [
        'FirstName', 'LastName', 'Email',
    ];~~~
Best Answer (As Selected By ziben69)
newbie360

oh ok, try this

YourModel::whereRaw('email REGEXP concat("(", firstname, "|", lastname, ")+.*@")')->get();
ziben69

Ok I have got now 3 arrays from one table:

  1. Firstname, 2. Lastname, 3. Email
public function getFirstName(){
        return $this->model->get(['firstname']);
    }

public function getLastName(){
        return $this->model->get(['lastname']);
    }

public function getMails(){
        return $this->model->get(['mail']);
    }

maybe have you solution how can I make next step? Now I need remove records where mail include firstname or lastname

Thanks

Saikishore

Hi @ziben69,

Please correct me if I am wrong. I am understanding that you want to display all the emails not like either firstname or last name. Then, you have to this filtering Query itself by using subquery(self join).

ziben69

Yes, that's it :) Maybe I can better to present the problem:

i have table which include: id, firstname, lastname, email. I have ~100k personal data. I need to filter mails like this:

if mail (id 1) include firstname or lastname (still id 1) in mail = remove from view or table.

i am trying to do it in JavaScript but maybe its wrong idea, check this:

$(document).ready(function() {
    firstName = window.firstname;
    lastName = window.lastname;
    
    var names = [firstName];
    var emails = [email];
    var result = emails.filter(email => !names.find(name => email.includes(name)));
    
    console.log(result);
});

it doesn't work correct

 public function index(MailRepository $mailRepo)
    {
        JavaScript::put([
            'firstname' => $mailRepo->getFirstname(),
            'lastname' => $mailRepo->getLastname(),
            'email' => $mailRepo->getMail()
        ]);
        
        $firstNameOfMails = $mailRepo->getFirstname();
        $mails = $mailRepo->getAll();
        return view('mails.list', [
            "mailList" => $mails, "firstnameList" =>$firstNameOfMails
        ]);
    }

Can you help me do it by using subquery or just repair this what i have ;P?

ziben69

I'd love to use the subquery but I do not really understand how to construct this query.

Saikishore

I didn't test the below code... you can try this and you can manipulate if there are any error..

DB::table('personal_data' as  p1)
        ->join('personal_data as p2', function($join)
        {
            $join->on('p1.id', '=', 'p2.id')
                 ->where('p2.email', 'NOT LIKE', '%p1.first_name%')
        ->where('p2.email', 'NOT LIKE', '%p1.last_name%');
        })
        ->get();

ziben69

ah ok i got it DB::table('personal_data' as p1) error with ', but now i have next error :D

Class 'App\Http\Controllers\DB' not found

Edit:

use DB;

now need to display it. I have something like this:

public function index(){
       
        return DB::table('mails as  m1')->join('mails as m2', function ($join) {
            $join->on('m1.id', '=', 'm2.id')
            ->where('m2.email', 'NOT LIKE', '%m1.firstname%')
            ->where('m2.email', 'NOT LIKE', '%m1.lastname%');
        })->get();
    }

BTW it gave to me full list, nothing removed

Saikishore

You can find the error easily if you are trying to figure out the error

DB::table('personal_data as p1')

Saikishore

What your getting from Query. can you test this once ..

            $join->on('m1.id', '=', 'm2.id')
            ->where('m2.email', 'NOT LIKE', '%m1.firstname%')
            ->where('m2.email', 'NOT LIKE', '%m1.lastname%');
        })->get();

dd($emails);

ziben69
Collection {#6559 ▼
  #items: array:5938 [▼
    0 => {#620 …6}
    1 => {#621 …6}
    2 => {#622 …6}
    . . .
    2498 => {#3118 …6}
    2499 => {#3119 …6}
     …3438
  ]
}

number of records in my DB = 5938 here i get the same

Saikishore

ooh... May be we need to change the query. I am not sure about it will work or not..

 $join->on('m1.id', '=', 'm2.id')
            ->where('m2.email', 'NOT LIKE', '%m1.firstname%')
            ->orWhere('m2.email', 'NOT LIKE', '%m1.lastname%');
ziben69

Ops:

Symfony \ Component \ Debug \ Exception \ FatalErrorException (E_UNKNOWN) Allowed memory size of 134217728 bytes

GET http://laravel.local/mails 500 (Internal Server Error)

Haha :D

ziben69

i don't know to fix it... Query doesn't work correct.

newbie360

may be you can try this, also make sure the collections count is correct, if the collections correct, can try put this to a callback delete where id in

$collections = YourModel::whereRaw('email REGEXP concat("^(", firstname, "|", lastname, ")@")')->get();

PS, just in case something wrong, backup your db before to do delete action

ziben69

hmm it works but it choose only for example:

Firstname: john Lastname: doe mail: [email protected]

and it doesn't choose something like: [email protected]

newbie360

hmm are you sure the pattern is john and following a . and doe ?

and what about doe.john ?

or only check john or doe in the email string ? (this is danger too)

if the john.doe just a random string, i feeling abit danger

Please sign in or create an account to participate in this conversation.