Store top categories from an API request into the database

I have an API request like this:

$postInformation = (new postInformation)->handle(['field' => 'slug', 'value' => $this->slug]);

A dump of this response, besides other things, show some categories that are coming from this API request. And they are on this format where there is a 'categories' key with the top categories:

categories:
^ array:40 [
  "id" => 2
  ...
  "categories" => array:2 [
    "data" => array:15 [
      0 => array:3 [
        "id" => 6
        "name" => array:1 [
          "en" => "General"
        ]
        "on_request" => 0
      ]
      1 => array:3 [
        "id" => 14
        "name" => array:1 [
          "en" => "Tuts"
        ]
        "on_request" => 0
      ]
      2 => array:3 [
        "id" => 3
        "name" => array:1 [
          "en" => "Laravel"
        ]
        "on_request" => 0
      ]
      
      ...
  ]
  ...
 ]

I created a table 'post_top_categories' and a model PostTopCategory and I want to get the top categories from the API response above and store them into the 'post_top_categories' table. But I'm not understanding how to properly achieve this. Do you know how it can be achieved it? Thanks

SilenceBringer

4 years ago

Best Answer

Level 55

@bernardev what's the problem to store it? What did you try already?

So, $postInformation is array with categories key and you want to store everything inside into your db?

if you do not need ids of inserted rows - just use mass insert https://laravel.com/docs/9.x/queries#insert-statements

DB::table('post_top_categories')->insert(
	array_map(fn ($category) => [
		'id' => $category['id'],
		'name' => $category['name']['en'],
		// other fields according to your table fields
	], $postInformation['categories'])
)

1 like

bernardev

4 years ago

Level 1

@SilenceBringer Thanks, I already tried different ways of fetch the data from that structure and transform them in a way to store them in the database but always getting issues. Also with your example its showing :

{message: "Undefined array key "id"", exception: "ErrorException",…} exception: "ErrorException" file: "/var/www/html/app/Nova/Post" line: 65 message: "Undefined array key "id""

bernardev

4 years ago

Level 1

@SilenceBringer Thanks, it was only necessary to add 'data' here: $postInformation['categories']['data']])

DB::table('post_top_categories')->insert( array_map(fn ($category) => [ 'id' => $category['id'], 'name' => $category['name']['en'], // other fields according to your table fields ], $postInformation['categories']['data']]) )

bernardev

4 years ago

Level 1

@SilenceBringer But its strange, not it seems that the records were inserted on the database. But every time the page is refreshed it shows an error:

message: "Undefined array key "categories"", exception: "ErrorException",…} exception: "ErrorException" file: "/var/www/html/app/Nova/Post" line: 68 message: "Undefined array key "categories"" trace: [{,…}, {file: "/var/www/html/app/Nova/{Post", line: 68,…},…]

bernardev

4 years ago

Level 1

@SilenceBringer Also do you know how to create an index so that is possible to have only one record with the same combination of post_id and category_name?

The migration is like this at the moment:

 public function up()
    {
        Schema::create('post_categories', function (Blueprint $table) {
            $table->id();
            
            $table->foreignId('post_id')
            ->nullable()
            ->constrained('posts');

            $table->unsignedSmallInteger('category_id')->nullable();
            $table->string('category_name')->nullable();
}

Please or to participate in this conversation.