Extraction of data from database by criteria with Laravel

Question

Hi I tried to write some query where i want to extract emails from accounts by relevant criteria. So for example if i want to send email by city i can choose city and sent email to people from this city but if i dont choose any city it should not recognized this and sent to all people.

My Question is there best way to extraction of data from base by criteria? Should I wrote one query or what is the best way to do that?

this all inputs is optional so if admin not choose any of input then this should return all emails from database but if he choose for example city then this should narrowin to this city — Adrian, Jul 26 '16 at 10:35

Anthony · Accepted Answer · 2016-07-26T11:29:52.287

I have no clue what you are really doing, but based on a basic understanding of Laravel and what it seems like you are trying to do with different "flags" or "criteria", doesn't it make sense to continue adding to the main collection based on each criteria, and then fetch the email addresses once the main collection's query properties are all added in. For example:

// Start with your Account, ordered by ID:

$accounts = Account::orderBy('id');

// if a city ID is provided, add this to the "where" section of the main collection:

if($this->city_id !== NULL) {
    $accounts->where('location_id', $this->city_id);
}

// if an Account Type is provided, add this to the "where" section of the main collection:

if ($this->account_type !== NULL) {
    $accounts->where('type_id', $this->account_type);
}

// if Premium Only is set, add this to the "where" section of the main collection:
if ($this->only_premium == TRUE) {
    $accounts->where('is_premium', $this->only_premium);
}

// if Free Only is set, add this to the "where" section of the main collection:

if ($this->only_free == TRUE) {
    $accounts->where('is_premium', $this->only_free);
}

/* For those last two, it's a bit confusing whether only_free and     only_premium are mutually exclusive, 
 so maybe consider having one property that toggles between Free/Premium 
(assuming it can't be both but must be one or the other). */

// Finally, pull the emails (the "plucking" off of the final $accounts collection variable:

    $emailAccounts = $accounts->get()->pluck('email')->toArray();

I am fairly certain that at least one part of this example code is fundamentally wrong, but that it is also fundamentally more logical and clear for what you are wanting to do.

i tried to do that but this way the data not taken from database correctly. For example if i take city=new york and accoutpremium= true then i have all emails with city= new york but i have additionaly emails with accout=premium where people are from other cities but they just added to array becosue they have premium — Adrian, Jul 26 '16 at 11:10
You need to get the raw query that it's sending. It looks like the following SO answer covers this : http://stackoverflow.com/a/18236656/49478 — Anthony, Jul 26 '16 at 11:17
so the query you are wanting is something like `SELECT * FROM accounts WHERE location_id = "New York City" AND is_premium = 1` But instead you are getting results suggesting that the query being executed is more like `SELECT * FROM accounts WHERE location_id = "New York City" OR is_premium = 1`, So that instead of getting results where both conditions are true, you are getting results where either condition is true, right? — Anthony, Jul 26 '16 at 11:22
The best thanks come from accepted answers and feedback on what specifically fixed the issue in your situation. — Anthony, Jul 26 '16 at 11:26

Extraction of data from database by criteria with Laravel

1 Answers1