Asked  7 Months ago    Answers:  5   Viewed   163 times

Introduction

What up folks, I got a question about model factories and multiple unique columns:

Background

I have a model named Image. This model has language support stored in a separate model, ImageText. ImageText has an image_id column, a language column and a text column.

ImageText has a constraint in MySQL that the combination image_id and language has to be unique.

class CreateImageTextsTable extends Migration
{

    public function up()
    {
        Schema::create('image_texts', function ($table) {

            ...

            $table->unique(['image_id', 'language']);

            ...

        });
    }

    ...

Now, I want each Image to have several ImageText models after seeding is done. This is easy with model factories and this seeder:

factory(AppModelsImage::class, 100)->create()->each(function ($image) {
    $max = rand(0, 10);
    for ($i = 0; $i < $max; $i++) {
        $image->imageTexts()->save(factory(AppModelsImageText::class)->create());
    }
});

Problem

However, when seeding this using model factories and faker, you are often left with this message:

[PDOException]                                                                                                                 
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '76-gn' for key 'image_texts_image_id_language_unique'

This is because at some point, inside that for loop, the faker will random the same languageCode twice for an image, breaking the unique constraint for ['image_id', 'language'].

You can update your ImageTextFactory to say this:

$factory->define(AppModelsImageText::class, function (FakerGenerator $faker) {

    return [
        'language' => $faker->unique()->languageCode,
        'title' => $faker->word,
        'text' => $faker->text,
    ];
});

But then, you instead get the problem that the faker will run out of languageCodes after enough imageTexts have been created.

Current solution

This is currently solved by having two different factories for the ImageText, where one resets the unique counter for languageCodes and the seeder calls the factory which resets te unique counter before entering the for loop to create further ImageTexts. But this is code duplication, and there should be a better way to solve this.

The question

Is there a way to send the model you are saving on into the factory? If so, I could have a check inside the factory to see if the current Image has any ImageTexts attached already and if it doesn't, reset the unique counter for languageCodes. My goal would be something like this:

$factory->define(AppModelsImageText::class, function (FakerGenerator $faker) {

    $firstImageText = empty($image->imageTexts());

    return [
        'language' => $faker->unique($firstImageText)->languageCode,
        'title' => $faker->word,
        'text' => $faker->text,
    ];
});

Which of course currently gives:

[ErrorException]           
Undefined variable: image

Is it possible to achieve this somehow?

 Answers

23

I solved it

I searched a lot for a solution to this problem and found that many others also experienced it. If you only need one element on the other end of your relation, it's very straight forward.

The addition of the "multi column unique restriction" is what made this complicated. The only solution I found was "Forget the MySQL restriction and just surround the factory creation with a try-catch for PDO-exceptions". This felt like a bad solution since other PDOExceptions would also get caught, and it just didn't feel "right".

Solution

To make this work I divided the seeders to ImageTableSeeder and ImageTextTableSeeder, and they are both very straight forward. Their run commands both look like this:

public function run()
{
    factory(AppModelsImageText::class, 100)->create();
}

The magic happens inside the ImageTextFactory:

$factory->define(AppModelsImageText::class, function (FakerGenerator $faker) {

    // Pick an image to attach to
    $image = AppModelsImage::inRandomOrder()->first();
    $image instanceof AppModelsImage ? $imageId = $image->id : $imageId = null;

    // Generate unique imageId-languageCode combination
    $imageIdAndLanguageCode = $faker->unique()->regexify("/^$imageId-[a-z]{2}");
    $languageCode = explode('-', $imageIdAndLanguageCode)[1];

    return [
        'image_id' => $imageId,
        'language' => $languageCode,
        'title' => $faker->word,
        'text' => $faker->text,
    ];
});

This is it:

$imageIdAndLanguageCode = $faker->unique()->regexify("/^$imageId-[a-z]{2}");

We use the imageId in a regexify-expression and add whatever is also included in our unique combination, separated in this case with a '-' character. This will generate results like "841-en", "58-bz", "96-xx" etc. where the imageId is always a real image in our database, or null.

Since we stick the unique tag to the language code together with the imageId, we know that the combination of the image_id and the languageCode will be unique. This is exactly what we need!

Now we can simply extract the created language code, or whatever other unique field we wanted to generate, with:

$languageCode = explode('-', $imageIdAndLanguageCode)[1];

This approach has the following advantages:

  • No need to catch exceptions
  • Factories and Seeders can be separated for readability
  • Code is compact

The disadvantage here is that you can only generate key combinations where one of the keys can be expressed as regex. As long as that's possible, this seems like a good approach to solving this problem.

Saturday, May 29, 2021
 
PLPeeters
answered 7 Months ago
61

You can use attach() or sync() method on a many-to-many relationship.

There are multiple ways you can approach this. Here one of them:

// Populate roles
factory(AppRole::class, 20)->create();

// Populate users
factory(AppUser::class, 50)->create();

// Get all the roles attaching up to 3 random roles to each user
$roles = AppRole::all();

// Populate the pivot table
AppUser::all()->each(function ($user) use ($roles) { 
    $user->roles()->attach(
        $roles->random(rand(1, 3))->pluck('id')->toArray()
    ); 
});
Sunday, July 11, 2021
 
Rocket
answered 5 Months ago
51

You are using join instead of left join

Try this

DB::table('A')
->leftjoin('B', 'A.program_id', '=', 'B.program_id')
->select('A.program_id')
->whereNull('B.program_id')
->where('A.student_id', '=', 5)
->get()->toArray();

It will produce a query like enter image description here

Saturday, August 7, 2021
 
Parfait
answered 4 Months ago
99

I'm not sure what is the problem. It works fine here:

import sqlite3

# connect to memory-only database for testing
con = sqlite3.connect('')
cur = con.cursor()

# create the table
cur.execute('''
CREATE TABLE CorpWalletJournal (
    date INT, refID INT, refTypeID INT, ownerName1 TEXT, 
    ownerID1 INT, ownerName2 TEXT, ownerID2 INT, argName1 TEXT, 
    argID1 ID, amount INT, balance INT, reason TEXT, accountKey INT, 
    UNIQUE (ownerID1, ownerID2, accountKey, argID1)
);
''')
con.commit()

insert_sql = '''INSERT INTO CorpWalletJournal 
(date, refID, refTypeID, ownerName1, ownerID1, ownerName2, ownerID2, 
argName1, argID1, amount, balance, reason, accountKey)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'''

## create 5 rows changing only argID1 - it works:
for argid in xrange(5): 
    cur.execute(insert_sql, (1, 1, 1, 'a', 1, 'a', 1, 'a', argid, 1, 1, 'a', 1))
con.commit()

# now try to insert a row that is already there:
cur.execute(insert_sql,  (1, 1, 1, 'a', 1, 'a', 1, 'a', 0, 1, 1, 'a', 1))

The error I get from last line is:

Traceback (most recent call last):
  File "teststdio.py", line 41, in <module>
    cur.execute(insert_sql,  (1, 1, 1, 'a', 1, 'a', 1, 'a', 0, 1, 1, 'a', 1))
sqlite3.IntegrityError: columns ownerID1, ownerID2, accountKey, argID1 
    are not unique
Tuesday, October 5, 2021
 
Poul K. Sørensen
answered 2 Months ago
27

Here's something that might work.

$factory->define(AppAliasCommand::class, function (FakerGenerator $faker) {
    static $order = 1;   
    return [
        'user_id' => AppUser::inRandomOrder()->first()->id,
        'command' => $faker->word,
        'content' => $faker->sentence,
        'order'   => $order++
    ];
});

It just keeps a counter internal to that function.

Update:

Laravel 8 introduced new factory classes so this request becomes:

class AliasCommandFactory extends Factory {

    private static $order = 1;

    protected $model = AliasCommand::class;

    public function definition() {
         $faker = $this->faker;
         return [
            'user_id' => User::inRandomOrder()->first()->id,
            'command' => $faker->word,
            'content' => $faker->sentence,
            'order'   => self::$order++
        ];
    }
}
Wednesday, November 3, 2021
 
Jeff Yates
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share