Discover Top Posts Tagged with #eloquent orm

json_extract() and laravel eloquent with example

#laravel #laravel framework #laravel projects #json #jsonapi #oop #mysql #eloquent orm #tutorial #guide

Json and Laravel Eloquent with example

JSON is short form of JavaScript Object Notation, which is used to store data and since it is very lightweight hence majorly used for transporting data to and from a web server. In earlier days, XML was used for this very purpose but writing and reading XML data was very tedious while JSON on the other hand, is self describing.

In today’s topic I am going to show you how to store JSON data into MySQL and access those data using laravel eloquent query builders.

Storing JSON data in MySQL using Laravel

First let’s create a dummy table using migration command.

Schema::create(json_examples, function (Blueprint $table) { $table->bigIncrements('id'); $table->string('name'); $table->text('json_details'); //using text datatype to store json $table->timestamps(); });

As you can see I used text datatype for storing json.

Next, let’s create a corresponding Model for the same

php artisan make:model JsonExample

JsonExample.php

public static function create($data) { $new = new self(); $new->name = $data['name']; $new->json_details = $data['json_details'']; $new->save(); }

So in controller we can do something like this:

public function storeEmpData(Request $request) { ... $name = $request->name'; $details = ['emp_code' => '001', 'date_of_joining' => Carbon::parse($request->doj), 'salary' => '50000']; $dataToStore = [ 'name' => $name, 'Json_details' => json_encode($details) ]; //saving data JsonExample::create($dataToStore); }

Processing Json data in Laravel eloquent.

Json data in where()

We can easily apply conditions on json data in laravel using -> (arrow notation), for example, fetch records where salary is more than 50000.

JsonExample.php

public static function getSalaryMoreThan($salary) { return self::where('json_details->salary','>',$salary)->get(); }

Access json data using json_extract()

Another example, we need records of employees who joined the company before a date for instance 9th November 2018.

JsonExample.php

public static function getEmployeesBeforeDate($date) { return self::whereDate("json_extract('json_details', '$.doj')", '<', $date)->get() }

As you can see above, I used json_extract() method which is MySQL’s function to extract a field from JSON column since I can not simply instruct eloquent using arrow operator like

return self::whereDate('json_details->doj', '<', $date)->get()

Rather I have to explicitly tell eloquent to extract json field and then proceed.

JSON data in DB::raw()

Many times we need to use MySQL’s aggregate functions like SUM() as per the requirement, in that case we use DB facade. For example,

select(DB::raw('sum(...)'))

How to access json field in MySQL’s aggregate function ?

It is very simple, let’s take an example, we need to sum total salary of employees.

public static function findTotalSalary() { return self::select(DB:raw('sum("json_extract('json_details', '$.salary')") as total_salary'))->get(); }

Conclusion

That’s all about this topic friends, I hope you learned something new which you haven’t thought about. I thought I should share this knowledge as I encountered such problem while working on a project today where I do save additional data in json format. Do comment your reviews and experiences below.

Thank you :)

Ebooks available now

You can download this article’s PDF eBooks for offline reading from below:

Issuu

Slide Share

Edocr

AuthorStream

Scribd

#laravel #laravel framework #laravel projects #eloquent orm #development #web development #programming #PHP Developers #php #mysql #json #javascript #oop

Laravel From Scratch [Part 6] – Fetching Data With Eloquent In this video I will show you how to start working with Eloquent which is an ORM (Object Relational Mapper) and it makes working with the database models ... source

#eloquent orm #Laravel #laravel database #laravel eloquent #object relational mapper #ORM

Laravel 5.8 Tutorial From Scratch - e15 - Eloquent Accessors & RESTful Controller - Part 1 - Laravel

Laravel 5.8 Tutorial From Scratch – e15 – Eloquent Accessors & RESTful Controller – Part 1 – Laravel

[ad_1]

It’s time for us to tackle a refactor of our controllers and views to implement a RESTful controller approach. Follow along as we implement the index, create and store methods in our controller and properly store our views, following the same approach. As a last bit, let’s write an accessor…

View On WordPress

Laravel 5.8 Tutorial From Scratch - e14 - Eloquent BelongsTo & HasMany Relationships - Laravel

Laravel 5.8 Tutorial From Scratch – e14 – Eloquent BelongsTo & HasMany Relationships – Laravel

[ad_1]

In this episode, we are starting out by reviewing what we have learned so far to create a company model and migration. For new content, we are tackling connecting the company to a customer using the belongsTo and hasMany eloquent relationship.

For the best experience, follow along in our…

View On WordPress

How It Works: Laravel Eloquent ORM [TutsPlus]

Laravel’s Eloquent object-relational mapper (ORM) is one of the most-loved features of the framework. Eloquent makes it easy to connect to relational data in a database and work with it using Object-Oriented models in your Laravel app. It is simple to set up, easy to use, and packs a lot of power.

In this Coffee Break Course, we’ll take a look at…

View On WordPress

#Eloquent ORM #How It Works #laravel #Laravel Eloquent ORM

How It Works: Laravel Eloquent ORM [TutsPlus]

In this Coffee Break Course, we’ll take a look at…

View On WordPress

#Eloquent ORM #How It Works #laravel #Laravel Eloquent ORM

How It Works: Laravel Eloquent ORM [TutsPlus]

In this Coffee Break Course, we’ll take a look at…

View On WordPress

#Eloquent ORM #How It Works #laravel #Laravel Eloquent ORM

Json and Laravel Eloquent with example

In today’s topic I am going to show you how to store JSON data into MySQL and access those data using laravel eloquent query builders.

Storing JSON data in MySQL using Laravel

First let’s create a dummy table using migration command.

As you can see I used text datatype for storing json.

Next, let’s create a corresponding Model for the same

php artisan make:model JsonExample

JsonExample.php

public static function create($data) { $new = new self(); $new->name = $data['name']; $new->json_details = $data['json_details'']; $new->save(); }

So in controller we can do something like this:

Processing Json data in Laravel eloquent.

Json data in where()

We can easily apply conditions on json data in laravel using -> (arrow notation), for example, fetch records where salary is more than 50000.

JsonExample.php

public static function getSalaryMoreThan($salary) { return self::where('json_details->salary','>',$salary)->get(); }

Access json data using json_extract()

Another example, we need records of employees who joined the company before a date for instance 9th November 2018.

JsonExample.php

public static function getEmployeesBeforeDate($date) { return self::whereDate("json_extract('json_details', '$.doj')", '<', $date)->get() }

As you can see above, I used json_extract() method which is MySQL’s function to extract a field from JSON column since I can not simply instruct eloquent using arrow operator like

return self::whereDate('json_details->doj', '<', $date)->get()

Rather I have to explicitly tell eloquent to extract json field and then proceed.

JSON data in DB::raw()

Many times we need to use MySQL’s aggregate functions like SUM() as per the requirement, in that case we use DB facade. For example,

select(DB::raw('sum(...)'))

How to access json field in MySQL’s aggregate function ?

It is very simple, let’s take an example, we need to sum total salary of employees.

public static function findTotalSalary() { return self::select(DB:raw('sum("json_extract('json_details', '$.salary')") as total_salary'))->get(); }

Conclusion

Thank you :)

Ebooks available now

You can download this article’s PDF eBooks for offline reading from below:

Issuu

Slide Share

Edocr

AuthorStream

Scribd

#laravel #laravel framework #laravel projects #eloquent orm #development #web development #programming #PHP Developers #php #mysql #json #javascript #oop

#eloquent orm

Trending Tags

Recently Viewed Tags

#eloquent orm