Our database contains information about the languages used by businesses / service providers, and we have a section for "Bilingual" but we need to change that to be two separate items: "English" and "Spanish". The database is structured as a relational table:

We use Laravel for the backend of the web-app, so if we were to query the DB and get the languages it would look like:

$business = Business::all();
foreach($business as $b) {
    //gives us a collection of languages 
    $b->language();
}

What I am trying to do is write a one-time SQL query that finds all instances of a business having "Bilingual" as a language, add "English" and "Spanish" as the language (if it doesn't already exist), and delete "Bilingual".

Is such a one-time-query possible, or do I have to write a script to do it for me for each business?

CodePudding user response：

For this particular example there is easy solution :

$businesses = Business::with('languages')->get();
$english_id = Language::where('language_name','English')->first()->id;
$spanish_id = Language::where('language_name','Spanish')->first()->id;
$bilingual_id = Language::where('language_name','Bilingual')->first()->id;


foreach($businesses as $business) {
    $is_bilingual = $business->languages->where('id', $bilingual_id)->first();
    if($is_bilingual) {
        $business->languages()->sync([$english_id,$spanish_id]); 
    }
}

CodePudding user response：

Give this a try.

$business = Business::all();
$language_models = Languages::all();

$english_id = $language_models->find(function($language) {
    return $language->name() == 'English';
})->pluck('id');

$spanish_id = $language_models->find(function($language) {
    return $language->name() == 'Spanish';
})->pluck('id');

$bilingual_id = $language_models->find(function($language) {
    return $language->name() == 'Bilingual';
})->pluck('id');

foreach($business as $b) use($english_id, $spanish_id, $bilingual_id) {
    $languages = $b->language()->map(function($language) {
        return $language->name();
    });

    if (in_array('Bilingual', $languages) {
        if (!in_array('English', $languages)) {
            $b->language()->attach($english_id);
        }
        if (!in_array('Spanish', $languages)) {
            $b->language()->attach($spanish_id);
        }
        $b->language()->detach($bilingual_id);
    }
}

Id write whats going on here but its pretty self explanatory.

You will need to have a name method on your language model or change the code to match.