One place for hosting & domains

      Working

      Working with JSON in MySQL


      Introduction

      SQL databases tend to be rigid.

      If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it’s called structured query language.

      On the other side of the horizon, we have the NoSQL databases, also called schema-less databases that encourage flexibility. In schema-less databases, there is no imposed structural restriction, only data to be saved.

      Though every tool has it’s use case, sometimes things call for a hybrid approach.

      What if you could structure some parts of your database and leave others to be flexible?

      MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that.

      In this tutorial, you are going to learn.

      1. How to design your database tables using JSON fields.
      2. The various JSON based functions available in MYSQL to create, read, update, and delete rows.
      3. How to work with JSON fields using the Eloquent ORM in Laravel.

      Why Use JSON

      At this moment, you are probably asking yourself why would you want to use JSON when MySQL has been catering to a wide variety of database needs even before it introduced a JSON data type.

      The answer lies in the use-cases where you would probably use a make-shift approach.

      Let me explain with an example.

      Suppose you are building a web application where you have to save a user’s configuration/preferences in the database.

      Generally, you can create a separate database table with the id, user_id, key, and value fields or save it as a formatted string that you can parse at runtime.

      However, this works well for a small number of users. If you have about a thousand users and five configuration keys, you are looking at a table with five thousand records that addresses a very small feature of your application.

      Or if you are taking the formatted string route, extraneous code that only compounds your server load.

      Using a JSON data type field to save a user’s configuration in such a scenario can spare you a database table’s space and bring down the number of records, which were being saved separately, to be the same as the number of users.

      And you get the added benefit of not having to write any JSON parsing code, the ORM or the language runtime takes care of it.

      The Schema

      Before we dive into using all the cool JSON stuff in MySQL, we are going to need a sample database to play with.

      So, let’s get our database schema out of the way first.

      We are going to consider the use case of an online store that houses multiple brands and a variety of electronics.

      Since different electronics have different attributes(compare a Macbook with a Vacuumn Cleaner) that buyers are interested in, typically the Entity–attribute–value model (EAV) pattern is used.

      However, since we now have the option to use a JSON data type, we are going to drop EAV.

      For a start, our database will be named e_store and has three tables only named, brands, categories, and products respectively.

      Our brands and categories tables will be pretty similar, each having an id and a name field.

      CREATE DATABASE IF NOT EXISTS `e_store`
      DEFAULT CHARACTER SET utf8
      DEFAULT COLLATE utf8_general_ci;
      
      SET default_storage_engine = INNODB;
      
      CREATE TABLE `e_store`.`brands`(
          `id` INT UNSIGNED NOT NULL auto_increment ,
          `name` VARCHAR(250) NOT NULL ,
          PRIMARY KEY(`id`)
      );
      
      CREATE TABLE `e_store`.`categories`(
          `id` INT UNSIGNED NOT NULL auto_increment ,
          `name` VARCHAR(250) NOT NULL ,
          PRIMARY KEY(`id`)
      );
      

      The objective of these two tables will be to house the product categories and the brands that provide these products.

      While we are at it, let us go ahead and seed some data into these tables to use later.

      /* Brands */
      INSERT INTO `e_store`.`brands`(`name`)
      VALUES
          ('Samsung');
      
      INSERT INTO `e_store`.`brands`(`name`)
      VALUES
          ('Nokia');
      
      INSERT INTO `e_store`.`brands`(`name`)
      VALUES
          ('Canon');
      
      /* Types of electronic device */
      INSERT INTO `e_store`.`categories`(`name`)
      VALUES
          ('Television');
      
      INSERT INTO `e_store`.`categories`(`name`)
      VALUES
          ('Mobilephone');
      
      INSERT INTO `e_store`.`categories`(`name`)
      VALUES
          ('Camera');
      

      The brands table

      The categories table

      Next, is the business area of this tutorial.

      We are going to create a products table with the id, name, brand_id, category_id, and attributes fields.

      CREATE TABLE `e_store`.`products`(
          `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
          `name` VARCHAR(250) NOT NULL ,
          `brand_id` INT UNSIGNED NOT NULL ,
          `category_id` INT UNSIGNED NOT NULL ,
          `attributes` JSON NOT NULL ,
          PRIMARY KEY(`id`) ,
          INDEX `CATEGORY_ID`(`category_id` ASC) ,
          INDEX `BRAND_ID`(`brand_id` ASC) ,
          CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
          CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
      );
      

      Our table definition specifies foreign key constraints for the brand_id and category_id fields, specifying that they reference the brands and categories table respectively. We have also specified that the referenced rows should not be allowed to delete and if updated, the changes should reflect in the references as well.

      The attributes field’s column type has been declared to be JSON which is the native data type now available in MySQL. This allows us to use the various JSON related constructs in MySQL on our attributes field.

      Here is an entity relationship diagram of our created database.

      The e_store database

      Our database design is not the best in terms of efficiency and accuracy. There is no price column in the products table and we could do with putting a product into multiple categories. However, the purpose of this tutorial is not to teach database design but rather how to model objects of different nature in a single table using MySQL’s JSON features.

      The CRUD Operations

      Let us look at how to create, read, update, and delete data in a JSON field.

      Create

      Creating a record in the database with a JSON field is pretty simple.

      All you need to do is add valid JSON as the field value in your insert statement.

      /* Let's sell some televisions */
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Prime' ,
          '1' ,
          '1' ,
          '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Octoview' ,
          '1' ,
          '1' ,
          '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Dreamer' ,
          '1' ,
          '1' ,
          '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Bravia' ,
          '1' ,
          '1' ,
          '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Proton' ,
          '1' ,
          '1' ,
          '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
      );
      

      The products table after adding televisions

      Instead of laying out the JSON object yourself, you can also use the built-in JSON_OBJECT function.

      The JSON_OBJECT function accepts a list of key/value pairs in the form JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) and returns a JSON object.

      /* Let's sell some mobilephones */
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Desire' ,
          '2' ,
          '2' ,
          JSON_OBJECT(
              "network" ,
              JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
              "body" ,
              "5.11 x 2.59 x 0.46 inches" ,
              "weight" ,
              "143 grams" ,
              "sim" ,
              "Micro-SIM" ,
              "display" ,
              "4.5 inches" ,
              "resolution" ,
              "720 x 1280 pixels" ,
              "os" ,
              "Android Jellybean v4.3"
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Passion' ,
          '2' ,
          '2' ,
          JSON_OBJECT(
              "network" ,
              JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
              "body" ,
              "6.11 x 3.59 x 0.46 inches" ,
              "weight" ,
              "145 grams" ,
              "sim" ,
              "Micro-SIM" ,
              "display" ,
              "4.5 inches" ,
              "resolution" ,
              "720 x 1280 pixels" ,
              "os" ,
              "Android Jellybean v4.3"
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Emotion' ,
          '2' ,
          '2' ,
          JSON_OBJECT(
              "network" ,
              JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
              "body" ,
              "5.50 x 2.50 x 0.50 inches" ,
              "weight" ,
              "125 grams" ,
              "sim" ,
              "Micro-SIM" ,
              "display" ,
              "5.00 inches" ,
              "resolution" ,
              "720 x 1280 pixels" ,
              "os" ,
              "Android KitKat v4.3"
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Sensation' ,
          '2' ,
          '2' ,
          JSON_OBJECT(
              "network" ,
              JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
              "body" ,
              "4.00 x 2.00 x 0.75 inches" ,
              "weight" ,
              "150 grams" ,
              "sim" ,
              "Micro-SIM" ,
              "display" ,
              "3.5 inches" ,
              "resolution" ,
              "720 x 1280 pixels" ,
              "os" ,
              "Android Lollypop v4.3"
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Joy' ,
          '2' ,
          '2' ,
          JSON_OBJECT(
              "network" ,
              JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
              "body" ,
              "7.00 x 3.50 x 0.25 inches" ,
              "weight" ,
              "250 grams" ,
              "sim" ,
              "Micro-SIM" ,
              "display" ,
              "6.5 inches" ,
              "resolution" ,
              "1920 x 1080 pixels" ,
              "os" ,
              "Android Marshmallow v4.3"
          )
      );
      

      The products table after adding mobilephones

      Notice the JSON_ARRAY function which returns a JSON array when passed a set of values.

      If you specify a single key multiple times, only the first key/value pair will be retained. This is called normalizing the JSON in MySQL’s terms. Also, as part of normalization, the object keys are sorted and the extra white-space between key/value pairs is removed.

      Another function that we can use to create JSON objects is the JSON_MERGE function.

      The JSON_MERGE function takes multiple JSON objects and produces a single, aggregate object.

      /* Let's sell some cameras */
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Explorer' ,
          '3' ,
          '3' ,
          JSON_MERGE(
              '{"sensor_type": "CMOS"}' ,
              '{"processor": "Digic DV III"}' ,
              '{"scanning_system": "progressive"}' ,
              '{"mount_type": "PL"}' ,
              '{"monitor_type": "LCD"}'
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Runner' ,
          '3' ,
          '3' ,
          JSON_MERGE(
              JSON_OBJECT("sensor_type" , "CMOS") ,
              JSON_OBJECT("processor" , "Digic DV II") ,
              JSON_OBJECT("scanning_system" , "progressive") ,
              JSON_OBJECT("mount_type" , "PL") ,
              JSON_OBJECT("monitor_type" , "LED")
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Traveler' ,
          '3' ,
          '3' ,
          JSON_MERGE(
              JSON_OBJECT("sensor_type" , "CMOS") ,
              '{"processor": "Digic DV II"}' ,
              '{"scanning_system": "progressive"}' ,
              '{"mount_type": "PL"}' ,
              '{"monitor_type": "LCD"}'
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Walker' ,
          '3' ,
          '3' ,
          JSON_MERGE(
              '{"sensor_type": "CMOS"}' ,
              '{"processor": "Digic DV I"}' ,
              '{"scanning_system": "progressive"}' ,
              '{"mount_type": "PL"}' ,
              '{"monitor_type": "LED"}'
          )
      );
      
      INSERT INTO `e_store`.`products`(
          `name` ,
          `brand_id` ,
          `category_id` ,
          `attributes`
      )
      VALUES(
          'Jumper' ,
          '3' ,
          '3' ,
          JSON_MERGE(
              '{"sensor_type": "CMOS"}' ,
              '{"processor": "Digic DV I"}' ,
              '{"scanning_system": "progressive"}' ,
              '{"mount_type": "PL"}' ,
              '{"monitor_type": "LCD"}'
          )
      );
      

      The products table after adding cameras

      There is a lot happening in these insert statements and it can get a bit confusing. However, it is pretty simple.

      We are only passing objects to the JSON_MERGE function. Some of them have been constructed using the JSON_OBJECT function we saw previously whereas others have been passed as valid JSON strings.

      In case of the JSON_MERGE function, if a key is repeated multiple times, it’s value is retained as an array in the output.

      A proof of concept is in order I suppose.

      /* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
      SELECT JSON_MERGE(
          '{"network": "GSM"}' ,
          '{"network": "CDMA"}' ,
          '{"network": "HSPA"}' ,
          '{"network": "EVDO"}'
      );
      

      We can confirm all our queries were run successfully using the JSON_TYPE function which gives us the field value type.

      /* output: OBJECT */
      SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;
      

      Add attributes are JSON objects

      Read

      Right, we have a few products in our database to work with.

      For typical MySQL values that are not of type JSON, a where clause is pretty straight-forward. Just specify the column, an operator, and the values you need to work with.

      Heuristically, when working with JSON columns, this does not work.

      /* It's not that simple */
      SELECT
          *
      FROM
          `e_store`.`products`
      WHERE
          attributes="{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}";
      

      When you wish to narrow down rows using a JSON field, you should be familiar with the concept of a path expression.

      The most simplest definition of a path expression(think JQuery selectors) is it’s used to specify which parts of the JSON document to work with.

      The second piece of the puzzle is the JSON_EXTRACT function which accepts a path expression to navigate through JSON.

      Let us say we are interested in the range of televisions that have atleast a single USB and HDMI port.

      SELECT
          *
      FROM
          `e_store`.`products`
      WHERE
          `category_id` = 1
      AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
      AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;
      

      Selecting records by JSON attributes

      The first argument to the JSON_EXTRACT function is the JSON to apply the path expression to which is the attributes column. The $ symbol tokenizes the object to work with. The $.ports.usb and $.ports.hdmi path expressions translate to “take the usb key under ports” and “take the hdmi key under ports” respectively.

      Once we have extracted the keys we are interested in, it is pretty simple to use the MySQL operators such as > on them.

      Also, the JSON_EXTRACT function has the alias -> that you can use to make your queries more readable.

      Revising our previous query.

      SELECT
          *
      FROM
          `e_store`.`products`
      WHERE
          `category_id` = 1
      AND `attributes` -> '$.ports.usb' > 0
      AND `attributes` -> '$.ports.hdmi' > 0;
      

      Update

      In order to update JSON values, we are going to use the JSON_INSERT, JSON_REPLACE, and JSON_SET functions. These functions also require a path expression to specify which parts of the JSON object to modify.

      The output of these functions is a valid JSON object with the changes applied.

      Let us modify all mobilephones to have a chipset property as well.

      UPDATE `e_store`.`products`
      SET `attributes` = JSON_INSERT(
          `attributes` ,
          '$.chipset' ,
          'Qualcomm'
      )
      WHERE
          `category_id` = 2;
      

      Updated mobilephones

      The $.chipset path expression identifies the position of the chipset property to be at the root of the object.

      Let us update the chipset property to be more descriptive using the JSON_REPLACE function.

      UPDATE `e_store`.`products`
      SET `attributes` = JSON_REPLACE(
          `attributes` ,
          '$.chipset' ,
          'Qualcomm Snapdragon'
      )
      WHERE
          `category_id` = 2;
      

      Updated mobilephones

      Easy peasy!

      Lastly, we have the JSON_SET function which we will use to specify our televisions are pretty colorful.

      UPDATE `e_store`.`products`
      SET `attributes` = JSON_SET(
          `attributes` ,
          '$.body_color' ,
          'red'
      )
      WHERE
          `category_id` = 1;
      

      Updated televisions

      All of these functions seem identical but there is a difference in the way they behave.

      The JSON_INSERT function will only add the property to the object if it does not exists already.

      The JSON_REPLACE function substitutes the property only if it is found.

      The JSON_SET function will add the property if it is not found else replace it.

      Delete

      There are two parts to deleting that we will look at.

      The first is to delete a certain key/value from your JSON columns whereas the second is to delete rows using a JSON column.

      Let us say we are no longer providing the mount_type information for cameras and wish to remove it for all cameras.

      We will do it using the JSON_REMOVE function which returns the updated JSON after removing the specified key based on the path expression.

      UPDATE `e_store`.`products`
      SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
      WHERE
          `category_id` = 3;
      

      Cameras after removing mount_type property

      For the second case, we also do not provide mobilephones anymore that have the Jellybean version of the Android OS.

      DELETE FROM `e_store`.`products`
      WHERE `category_id` = 2
      AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';
      

      We do not sell Jellybeans anymore!

      As stated previously, working with a specific attribute requires the use of the JSON_EXTRACT function so in order to apply the LIKE operator, we have first extracted the os property of mobilephones(with the help of category_id) and deleted all records that contain the string Jellybean.

      A Primer for Web Applications

      The old days of directly working with a database are way behind us.

      These days, frameworks insulate developers from lower-level operations and it almost feels alien for a framework fanatic not to be able to translate his/her database knowledge into an object relational mapper.

      For the purpose of not leaving such developers heartbroken and wondering about their existence and purpose in the universe, we are going to look at how to go about the business of JSON columns in the Laravel framework.

      We will only be focusing on the parts that overlap with our subject matter which deals with JSON columns. An in-depth tutorial on the Laravel framework is beyond the scope of this piece.

      Creating the Migrations

      Make sure to configure your Laravel application to use a MySQL database.

      We are going to create three migrations for brands, categories, and products respectively.

      $ php artisan make:migration create_brands
      $ php artisan make:migration create_categories
      $ php artisan make:migration create_products
      

      The create_brands and create_categories migrations are pretty similar and and a regulation for Laravel developers.

      /* database/migrations/create_brands.php */
      
      <?php
      
      use IlluminateSupportFacadesSchema;
      use IlluminateDatabaseSchemaBlueprint;
      use IlluminateDatabaseMigrationsMigration;
      
      class CreateBrands extends Migration
      {
          /**
           * Run the migrations.
           *
           * @return void
           */
          public function up()
          {
              Schema::create('brands', function(Blueprint $table){
                  $table->engine="InnoDB";
                  $table->increments('id');
                  $table->string('name');
                  $table->timestamps();
              });
          }
      
          /**
           * Reverse the migrations.
           *
           * @return void
           */
          public function down()
          {
              Schema::drop('brands');
          }
      }
      
      /* database/migrations/create_categories.php */
      
      <?php
      
      use IlluminateSupportFacadesSchema;
      use IlluminateDatabaseSchemaBlueprint;
      use IlluminateDatabaseMigrationsMigration;
      
      class CreateCategories extends Migration
      {
          /**
           * Run the migrations.
           *
           * @return void
           */
          public function up()
          {
              Schema::create('categories', function(Blueprint $table){
                  $table->engine="InnoDB";
                  $table->increments('id');
                  $table->string('name');
                  $table->timestamps();
              });
          }
      
          /**
           * Reverse the migrations.
           *
           * @return void
           */
          public function down()
          {
              Schema::drop('categories');
          }
      }
      
      

      The create_products migration will also have the directives for indexes and foreign keys.

      /* database/migrations/create_products */
      
      <?php
      
      use IlluminateSupportFacadesSchema;
      use IlluminateDatabaseSchemaBlueprint;
      use IlluminateDatabaseMigrationsMigration;
      
      class CreateProducts extends Migration
      {
          /**
           * Run the migrations.
           *
           * @return void
           */
          public function up()
          {
              Schema::create('products', function(Blueprint $table){
                  $table->engine="InnoDB";
                  $table->increments('id');
                  $table->string('name');
                  $table->unsignedInteger('brand_id');
                  $table->unsignedInteger('category_id');
                  $table->json('attributes');
                  $table->timestamps();
                  // foreign key constraints
                  $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
                  $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
                  // indexes
                  $table->index('brand_id');
                  $table->index('category_id');
              });
          }
      
          /**
           * Reverse the migrations.
           *
           * @return void
           */
          public function down()
          {
              Schema::drop('products');
          }
      }
      

      Pay attention to the $table->json('attributes'); statement in the migration.

      Just like creating any other table field using the appropriate data type named method, we have created a JSON column using the json method with the name attributes.

      Also, this only works for database engines that support the JSON data type.

      Engines, such as older versions of MySQL will not be able to carry out these migrations.

      Creating the Models

      Other than associations, there is not much needed to set up our models so let’s run through them quickly.

      /* app/Brand.php */
      
      <?php
      
      namespace App;
      
      use IlluminateDatabaseEloquentModel;
      
      class Brand extends Model
      {
          // A brand has many products
          public function products(){
              return $this->hasMany('Product')
          }
      }
      
      /* app/Category.php */
      
      <?php
      
      namespace App;
      
      use IlluminateDatabaseEloquentModel;
      
      class Category extends Model
      {
          // A category has many products
          public function products(){
              return $this->hasMany('Product')
          }
      }
      
      /* app/Product.php */
      
      <?php
      
      namespace App;
      
      use IlluminateDatabaseEloquentModel;
      
      class Product extends Model
      {
          // Cast attributes JSON to array
          protected $casts = [
              'attributes' => 'array'
          ];
      
          // Each product has a brand
          public function brand(){
              return $this->belongsTo('Brand');
          }
      
          // Each product has a category
          public function category(){
              return $this->belongsTo('Category');
          }
      }
      

      Again, our Product model needs a special mention.

      The $casts array which has the key attributes set to array makes sure whenever a product is fetched from the database, it’s attributes JSON is converted to an associated array.

      We will see later in the tutorial how this facilitates us to update records from our controller actions.

      Resource Operations

      Creating a Product

      Speaking of the admin panel, the parameters to create a product maybe coming in through different routes since we have a number of product categories. You may also have different views to create, edit, show, and delete a product.

      For example, a form to add a camera requires different input fields than a form to add a mobilephone so they warrant separate views.

      Moreover, once you have the user input data, you will most probabaly run it through a request validator, separate for the camera, and the mobilephone each.

      The final step would be to create the product through Eloquent.

      We will be focusing on the camera resource for the rest of this tutorial. Other products can be addressed using the code produced in a similar manner.

      Assuming we are saving a camera and the form fields are named as the respective camera attributes, here is the controller action.

      // creates product in database
      // using form fields
      public function store(Request $request){
          // create object and set properties
          $camera = new AppProduct();
          $camera->name = $request->name;
          $camera->brand_id = $request->brand_id;
          $camera->category_id = $request->category_id;
          $camera->attributes = json_encode([
              'processor' => $request->processor,
              'sensor_type' => $request->sensor_type,
              'monitor_type' => $request->monitor_type,
              'scanning_system' => $request->scanning_system,
          ]);
          // save to database
          $camera->save();
          // show the created camera
          return view('product.camera.show', ['camera' => $camera]);
      }
      

      Fetching Products

      Recall the $casts array we declared earlier in the Product model. It will help us read and edit a product by treating attributes as an associative array.

      // fetches a single product
      // from database
      public function show($id){
          $camera = AppProduct::find($id);
          return view('product.camera.show', ['camera' => $camera]);
      }
      

      Your view would use the $camera variable in the following manner.

      <table>
          <tr>
              <td>Name</td>
              <td>{{ $camera->name }}</td>
          </tr>
          <tr>
              <td>Brand ID</td>
              <td>{{ $camera->brand_id }}</td>
          </tr>
          <tr>
              <td>Category ID</td>
              <td>{{ $camera->category_id }}</td>
          </tr>
          <tr>
              <td>Processor</td>
              <td>{{ $camera->attributes['processor'] }}</td>
          </tr>
          <tr>
              <td>Sensor Type</td>
              <td>{{ $camera->attributes['sensor_type'] }}</td>
          </tr>
          <tr>
              <td>Monitor Type</td>
              <td>{{ $camera->attributes['monitor_type'] }}</td>
          </tr>
          <tr>
              <td>Scanning System</td>
              <td>{{ $camera->attributes['scanning_system'] }}</td>
          </tr>
      </table>
      

      Editing a Product

      As shown in the previous section, you can easily fetch a product and pass it to the view, which in this case would be the edit view.

      You can use the product variable to pre-populate form fields on the edit page.

      Updating the product based on the user input will be pretty similar to the store action we saw earlier, only that instead of creating a new product, you will fetch it first from the database before updating it.

      Searching Based on JSON Attributes

      The last piece of the puzzle that remains to discuss is querying JSON columns using the Eloquent ORM.

      If you have a search page that allows cameras to be searched based on their specifications provided by the user, you can do so with the following code.

      // searches cameras by user provided specifications
      public function search(Request $request){
          $cameras = AppProduct::where([
              ['attributes->processor', 'like', $request->processor],
              ['attributes->sensor_type', 'like', $request->sensor_type],
              ['attributes->monitor_type', 'like', $request->monitor_type],
              ['attributes->scanning_system', 'like', $request->scanning_system]
          ])->get();
          return view('product.camera.search', ['cameras' => $cameras]);
      }
      

      The retrived records will now be available to the product.camera.search view as a $cameras collection.

      Deleting a Product

      Using a non-JSON column attribute, you can delete products by specifying a where clause and then calling the delete method.

      For example, in case of an ID.

      AppProduct::where('id', $id)->delete();
      

      For JSON columns, specify a where clause using a single or multiple attributes and then call the delete method.

      // deletes all cameras with the sensor_type attribute as CMOS
      AppProduct::where('attributes->sensor_type', 'CMOS')->delete();
      }
      

      Curtains

      We have barely scratched the surface when it comes to using JSON columns in MySQL.

      Whenever you need to save data as key/value pairs in a separate table or work with flexible attributes for an entity, you should consider using a JSON data type field instead as it can heavily contribute to compressing your database design.

      If you are interested in diving deeper, the MySQL documentation is a great resource to explore JSON concepts futher.

      I hope you found this tutorial interesting and knowledgeable. Until my next piece, happy coding!



      Source link

      Working with Django Templates & Static Files


      In our getting started with Django tutorial, I showed you how to get a Django site up and running. The templates we rendered were very basic though.

      This is definitely not how you want your site to look like.

      How do you get your site to look better? Simple! Add some styling. In this tutorial, I will show you how to add some CSS and JavaScript to your Django templates in order to make them look much better. To do that, you first need to understand the concept of static files in Django.

      Setting up a Django Project

      Let’s set up our test Django project. First, create a folder called projects which is where our app will live.

      mkdir projects && cd projects
      

      Inside projects, let’s use virtualenv to create an environment for our app’s dependencies.

      virtualenv env --python python3
      

      NOTE: If you do not have virtualenv installed, install it using the command pip install virtualenv.

      Once that is done, activate the environment by running the activate shell script.

      source env/bin/activate
      

      If that command works, you should see an (env) prompt on your terminal.

      #(env)~/projects
      $
      

      Everything look fine? Awesome! Let’s now use pip to install Django into our environment.

      #(env)~/projects
      $ pip install django
      

      That command should install Django into your environment. As of the time of writing, the Django version is 1.10.4.

      We are then going to call the django-admin script to create our Django app. Let’s do that like this:

      #(env)~/projects
      $ django-admin startproject djangotemplates
      

      If you check your projects folder structure, you should now have a new folder called djangotemplates created by Django in addition to the earlier env folder we created.

      cd into djangotemplates.

      Your folder structure should now be similar to this:

      djangotemplates
      --djangotemplates
      ----**init**.py
      ----settings.py
      ----urls.py
      ----wsgi.py
      --manage.py
      

      All done? You are now ready to begin!

      Settings for managing static files

      Static files include stuff like CSS, JavaScript and images that you may want to serve alongside your site. Django is very opinionated about how you should include your static files. In this article, I will show how to go about adding static files to a Django application.

      Open the settings.py file inside the inner djangotemplates folder. At the very bottom of the file you should see these lines:

      # djangotemplates/djangotemplates/settings.py
      
      # Static files (CSS, JavaScript, Images)
      # https://docs.djangoproject.com/en/1.10/howto/static-files/
      
      STATIC_URL = '/static/'
      

      This line tells Django to append static to the base url (in our case localhost:8000) when searching for static files. In Django, you could have a static folder almost anywhere you want. You can even have more than one static folder e.g. one in each app. However, to keep things simple, I will use just one static folder in the root of our project folder. We will create one later. For now, let’s add some lines in the settings.py file so that it looks like this.

      # djangotemplates/djangotemplates/settings.py
      
      # Static files (CSS, JavaScript, Images)
      # https://docs.djangoproject.com/en/1.10/howto/static-files/
      
      STATIC_URL = '/static/'
      
      # Add these new lines
      STATICFILES_DIRS = (
          os.path.join(BASE_DIR, 'static'),
      )
      
      STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles')
      

      The STATICFILES_DIRS tuple tells Django where to look for static files that are not tied to a particular app. In this case, we just told Django to also look for static files in a folder called static in our root folder, not just in our apps.

      Django also provides a mechanism for collecting static files into one place so that they can be served easily. Using the collectstatic command, Django looks for all static files in your apps and collects them wherever you told it to, i.e. the STATIC_ROOT. In our case, we are telling Django that when we run python manage.py collectstatic, gather all static files into a folder called staticfiles in our project root directory. This feature is very handy for serving static files, especially in production settings.

      App setup

      Create a folder called static on the same level as the inner djangotemplates folder and the manage.py file. You should now have this structure:

      djangotemplates
      --djangotemplates
      ----**init**.py
      ----settings.py
      ----urls.py
      ----wsgi.py
      --static
      --manage.py
      

      Inside this folder is where we will have any custom CSS and JS we choose to write. On that note, let’s add two folders inside the static folder to hold our files, one called css and the other called js. Inside css, create a file called main.css. Add a main.js in the js folder as well. Your static folder should now look like this:

      --static
      ----css
      ------main.cs
      ----js
      ------main.js
      

      Once that is done, let’s create a new Django app called example that we will be working with. Do you remember how to do that? Don’t worry, it’s quite simple.

      #(env)~/projects/djangotemplates
      $ python manage.py startapp example
      

      Once that is done, you should have a folder called example alongside djangotemplates and static. And of course you should still be able to see the manage.py file.

      djangotemplates
      --djangotemplates
      ----**init**.py
      ----settings.py
      ----urls.py
      ----wsgi.py
      --example
      --static
      --manage.py
      

      We need to tell Django about our new app. Go to the inner djangotemplates folder, open up settings.py and look for INSTALLED_APPS. Add example under the other included apps.

      # djangotemplates/djangotemplates/settings.py
      
      DEBUG = True
      
      ALLOWED_HOSTS = []
      
      
      # Application definition
      
      INSTALLED_APPS = [
          'django.contrib.admin',
          'django.contrib.auth',
          'django.contrib.contenttypes',
          'django.contrib.sessions',
          'django.contrib.messages',
          'django.contrib.staticfiles',
          'example', # Add this line
      ]
      

      Just to recap, we now have the following folder structure:

      djangotemplates
      --djangotemplates
      ----**init**.py
      ----settings.py
      ----urls.py
      ----wsgi.py
      --example
      ----migrations
      ------**init**.py
      ----admin.py
      ----apps.py
      ----models.py
      ----tests.py
      ----views.py
      --static
      ----css
      ------main.cs
      ----js
      ------main.js
      --manage.py
      

      URL definition

      Let’s define a URL to go to our new app. Let’s edit djangotemplates/djangotemplates/urls.py to effect that.

      # djangotemplates/djangotemplates/urls.py
      
      from django.conf.urls import url, include # Add include to the imports here
      from django.contrib import admin
      
      urlpatterns = [
          url(r'^admin/', admin.site.urls),
          url(r'^', include('example.urls')) # tell django to read urls.py in example app
      ]
      

      After that, in the example app folder, create a new file called urls.py and add the following code:

      # djangotemplates/example/urls.py
      
      from django.conf.urls import url
      from example import views
      
      urlpatterns = [
          url(r'^$', views.HomePageView.as_view(), name="home"), # Notice the URL has been named
          url(r'^about/$', views.AboutPageView.as_view(), name="about"),
      ]
      

      The code we have just written tells Django to match the empty route (i.e localhost:8000) to a view called HomePageView and the route /about/ to a view called AboutPageView. Remember, Django views take in HTTP requests and return HTTP responses. In our case, we shall use a TemplateView that returns a Home Page template and another one for the About page. To do this, inside your example app folder, create another folder called templates. Inside the new templates folder, create two new files called index.html and about.html. Your example app folder should now have this structure:

      --example
      ----migrations
      ------**init**.py
      ----templates
      ------index.html
      ------about.html
      ----admin.py
      ----apps.py
      ----models.py
      ----tests.py
      ----urls.py
      ----views.py
      

      Inside the index.html, paste the following code:

      <!-- djangotemplates/example/templates/index.html-->
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
        <meta charset="UTF-8">
        <title>Welcome Home</title>
      </head>
      <body>
        <p>"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. 
          Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 
          Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. 
          Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
        </p>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
      </body>
      </html>
      

      Add this code to about.html:

      <!-- djangotemplates/example/templates/about.html-->
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>About Us</title>
      </head>
      <body>
        <p>
        We are a group of Django enthusiasts with the following idiosyncrasies:
      
        <ol>
          <li>We only eat bananas on Saturdays.</li>
          <li>We love making playing football on rainy days.</li>
        </ol>
        </p>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
      </body>
      </html>
      

      Notice how we are referring to our links for Go Home and About This Site in our templates. We can use Django’s automatic URL reverse lookup because we named our URLs in our urls.py. Neat, huh!

      We shall see the effect of this code in the next section.

      Wiring up the views

      Let’s add the final code to serve up our templates. We need to edit djangotemplates/example/views.py for this.

      # djangotemplates/example/views.py
      from django.shortcuts import render
      from django.views.generic import TemplateView # Import TemplateView
      
      # Add the two views we have been talking about  all this time :)
      class HomePageView(TemplateView):
          template_name = "index.html"
      
      
      class AboutPageView(TemplateView):
          template_name = "about.html"
      

      Now we can run our app. We first need to make Django’s default migrations since this is the first time we are running our app.

      #(env)~/projects/djangotemplates
      $ python manage.py migrate
      

      Once that is done, start your server.

      #(env)~/projects/djangotemplates
      $ python manage.py runserver
      

      Open your browser and navigate to http://localhost:8000. You should be able to see our home page.

      Clicking the links at the bottom should be able to navigate you between the pages. Here is the About page:

      Template Inheritance

      Let’s shift our focus to the templates folder inside the example app folder. At the moment, it contains two templates, index.html and about.html.

      We would like both these templates to have some CSS included. Instead of rewriting the same code in both of them, Django allows us to create a base template which they will both inherit from. This prevents us from having to write a lot of repeated code in our templates when we need to modify anything that is shared.

      Let’s create the base template now. Create a file called base.html in djangotemplates/example/templates. Write this code inside it:

      <!-- djangotemplates/example/templates/base.html -->
      
      {% load static %}
      <!DOCTYPE html>
      <html>
        <head>
          <meta charset="utf-8">
          <title>
            Django Sample Site - {% block title %}{% endblock %}
          </title>
      
          <script src="https://www.digitalocean.com/community/tutorials/{% static"js/main.js' %}"></script> <!-- This is how to include a static file -->
          <link rel="stylesheet" href="https://www.digitalocean.com/community/tutorials/{% static"css/main.css' %}" type="text/css" />
        </head>
        <body>
          <div class="container">
            {% block pagecontent %}
            {% endblock %}
          </div>
        </body>
      </html>
      

      The very first line in the file, {% load static %}, uses Django’s special template tag syntax to tell the template engine to use the files in the static folder in this template.

      In the title tag, we use a Django block. What this means is that in any Django template which inherits from this base template, any HTML which is inside a block named title will be plugged into the title block. The same goes for the body tag’s pagecontent block. If this sounds confusing, don’t worry. You will see it in action soon.

      If you are not running your Django server, run it by executing python manage.py runserver in your terminal. Go to http://localhost:8000. You should see the previous template.

      Now edit the index.html template to inherit from the base template.

      <!-- djangotemplates/example/templates/index.html -->
      
      {% extends 'base.html' %} <!-- Add this for inheritance -->
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
        <meta charset="UTF-8">
        <title>Welcome Home</title>
      </head>
      <body>
          <p>"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. 
            Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 
            Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. 
            Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
          </p>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
      </body>
      </html>
      

      Reload the page in your browser. Nothing appears! This is because Django expects your content to be written inside the blocks we defined in the base template so that they can be rendered. Edit the index.html to add the blocks:

      <!-- djangotemplates/example/templates/index.html -->
      
      {% extends 'base.html' %}
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
        <meta charset="UTF-8">
        <title>{% block title %}Welcome Home {% endblock %}</title>
      </head>
      <body>
        {% block pagecontent %}
          <p>"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. 
            Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 
            Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. 
            Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
          </p>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
        {% endblock %}
      </body>
      </html>
      

      Reload the page in the browser and voila! Your content should appear again!

      We can also edit the about.html template to use the same.

      <!-- djangotemplates/example/templates/about.html -->
      
      {% extends 'base.html' %} <!-- Add this for inheritance -->
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %}About Us {% endblock %}</title>
      </head>
      <body>
        {% block pagecontent %}
          <p>
          We are a group of Django enthusiasts with the following idiosyncrasies:
      
          <ol>
              <li>We only eat bananas on Saturdays.</li>
              <li>We love making playing football on rainy days.</li>
          </ol>
          </p>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
          <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
        {% endblock %}
      </body>
      </html>
      

      You should now see this on the About page:

      Which is exactly the same as before!

      However, now since both templates inherit from a base template, I can easily style them. Open up main.css in your css folder and add these styles:

      .container {
          background: #eac656;
          margin: 10 10 10 10;
          border: 3px solid black;
      }
      

      This will style the container div which we are loading our content into. Refresh your browser. You should see this:

      The Home Page:

      The About Page:

      Rendering templates with data from views

      You can use Django’s template engine to display data in very powerful ways. In this section, I will create a Django view that will pass data into a template. I will then show you how to access that data in the template and display it to the user.

      First things first, open up views.py in the example app folder. We will add a new view to serve data into our yet to exist data.html template. Modify the views.py file to look like this:

      # djangotemplates/example/views.py
      
      from django.shortcuts import render
      from django.views.generic import TemplateView
      
      class HomePageView(TemplateView):
          template_name = "index.html"
      
      class AboutPageView(TemplateView):
          template_name = "about.html"
      
      # Add this view
      class DataPageView(TemplateView):
          def get(self, request, **kwargs):
              # we will pass this context object into the
              # template so that we can access the data
              # list in the template
              context = {
                  'data': [
                      {
                          'name': 'Celeb 1',
                          'worth': '3567892'
                      },
                      {
                          'name': 'Celeb 2',
                          'worth': '23000000'
                      },
                      {
                          'name': 'Celeb 3',
                          'worth': '1000007'
                      },
                      {
                          'name': 'Celeb 4',
                          'worth': '456789'
                      },
                      {
                          'name': 'Celeb 5',
                          'worth': '7890000'
                      },
                      {
                          'name': 'Celeb 6',
                          'worth': '12000456'
                      },
                      {
                          'name': 'Celeb 7',
                          'worth': '896000'
                      },
                      {
                          'name': 'Celeb 8',
                          'worth': '670000'
                      }
                  ]
              }
      
              return render(request, 'data.html', context)
      

      We are using the same kind of view we used to render the other templates. However, we are now passing a context object to the render method. The key-value pairs defined in the context will be available in the template being rendered and we can iterate through them just like any other list.

      To finish this up, go to the urls.py file in the howdy app and add the URL pattern for our new view so that it looks like this:

      # djangotemplates/example/urls.py
      
      from django.conf.urls import url
      from example import views
      
      urlpatterns = [
          url(r'^$', views.HomePageView.as_view(), name="home"),
          url(r'^about/$', views.AboutPageView.as_view(), name="about"),
          url(r'^data/$', views.DataPageView.as_view(), name="data"),  # Add this URL pattern
      ]
      

      Finally, let’s create the template. In the templates folder, create a file called data.html and write this code inside it.

      <!-- djangotemplates/example/templates/data.html -->
      
      {% extends 'base.html' %}
      <!DOCTYPE html>
      <html>
        <head>
          <meta charset="utf-8">
          <title></title>
        </head>
        <body>
          {% block pagecontent %}
          <div class="table-div">
          <!-- We will display our data in a normal HTML table using Django's
          template for-loop to generate our table rows for us-->
            <table class="table">
              <thead>
                <tr>
                  <th>Celebrity Name</th>
                  <th>Net Worth</th>
                </tr>
              </thead>
              <tbody>
                {% for celebrity in data %}
                  <tr>
                    <td>{{ celebrity.name }}</td>
                    <td>{{ celebrity.worth }}</td>
                  </tr>
                {% endfor %}
              </tbody>
            </table>
          </div>
          {% endblock %}
        </body>
      </html>
      

      In data.html, you can see that we use what is essentially a for loop to go through the data list. Binding of values in Django templates is done using {{}} curly brackets much like in AngularJS.

      With your server running, go to http://localhost:8000/data/ to see the template.

      Including snippets into your templates

      We now have three templates, index.html, about.html and data.html. Let’s link them together using a simple navigation bar. First up, let’s write the code for the navigation bar in another HTML template.

      In the templates folder inside the example app, create a new folder called partials. Inside it, create a file called nav-bar.html. The templates folder structure should now be like this:

      templates
      ----index.html
      ----about.html
      ----data.html
      ----partials
      ------nav-bar.html
      

      Edit the nav-bar.html partial so that it contains this code:

      <!-- djangotemplates/example/templates/partials/nav-bar.html -->
      
      <div class="nav">
        <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a>
        <a href="https://www.digitalocean.com/community/tutorials/{% url"data' %}">View Data</a>
      </div>
      

      Including snippets in a template is very simple. We use the includes keyword provided by Django’s templating engine. Go ahead and modify index.html to this:

      <!-- djangotemplates/example/templates/index.html -->
      
      {% extends 'base.html' %}
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
        <meta charset="UTF-8">
        <title>{% block title %}Welcome Home {% endblock %}</title>
      </head>
      <body>
        {% block pagecontent %}
          <p>"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. 
            Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 
            Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. 
            Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
          </p>
          {% include 'partials/nav-bar.html' %} <!--Add this-->
      
          <!-- Remove these two lines -- >
          <!-- <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a> -->
          <!-- <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a> -->
        {% endblock %}
      </body>
      </html>
      

      Modify about.html to this:

      <!-- djangotemplates/example/templates/about.html -->
      
      {% extends 'base.html' %}
      
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %}About Us {% endblock %}</title>
      </head>
      <body>
        {% block pagecontent %}
          <p>
          We are a group of Django enthusiasts with the following idiosyncrasies:
      
          <ol>
              <li>We only eat bananas on Saturdays.</li>
              <li>We love making playing football on rainy days.</li>
          </ol>
          </p>
          {% include 'partials/nav-bar.html' %} <!--Add this-->
      
          <!-- Remove these two lines -- >
          <!-- <a href="https://www.digitalocean.com/community/tutorials/{% url"home' %}">Go Home</a> -->
          <!-- <a href="https://www.digitalocean.com/community/tutorials/{% url"about' %}">About This Site</a> -->
        {% endblock %}
      </body>
      </html>
      

      Lastly, modify data.html to this:

      <!-- djangotemplates/example/templates/data.html -->
      
      {% extends 'base.html' %}
      <!DOCTYPE html>
      <html>
        <head>
          <meta charset="utf-8">
          <title></title>
        </head>
        <body>
          {% block pagecontent %}
          <div class="table-div">
            <table class="table">
              <thead>
                <tr>
                  <th>Celebrity Name</th>
                  <th>Net Worth</th>
                </tr>
              </thead>
              <tbody>
                {% for celebrity in data %}
                  <tr>
                    <td>{{ celebrity.name }}</td>
                    <td>{{ celebrity.worth }}</td>
                  </tr>
                {% endfor %}
              </tbody>
            </table>
          </div>
          {% include 'partials/nav-bar.html' %} <!--Add this-->
          {% endblock %}
        </body>
      </html>
      

      Time to check out our work! Open your browser and navigate to http://localhost:8000. You should see this:

      All the pages are now linked with the navbar so you can easily navigate back and forth through them, all with minimal code written. Here is the data.html template:

      And here is about.html:

      Note: I have added the following CSS to syle the links in the navbar. Feel free to use it or play with your own styles:

      // djangtotemplates/static/css/main.css
      
      .container {
          background: #eac656;
          margin: 10 10 10 10;
          border: 3px solid black;
      }
      
      .nav a {
          background: #dedede;
      }
      

      Filters

      Filters take data piped to them and output it in a formatted way. Django templates have access to the humanize collection of filters, which make data more human readable. Let’s make the celebrity’s networth field in the data template more readable by using some of these filters.

      To use Django’s humanize filters, you first need to edit some settings. Open up djangotemplates/settings.py and edit the INSTALLED_APPS list to this:

      # djangotemplates/djangotemplates/settings.py
      
      ALLOWED_HOSTS = []
      
      
      # Application definition
      
      INSTALLED_APPS = [
          'django.contrib.admin',
          'django.contrib.auth',
          'django.contrib.contenttypes',
          'django.contrib.sessions',
          'django.contrib.messages',
          'django.contrib.staticfiles',
          'django.contrib.humanize', # Add this line. Don't forget the trailing comma
          'example',
      ]
      

      We can now use a filter in our templates. We are going to use the intcomma filter to add comma’s in large numbers to make them easier to read. Let’s modify data.html to this:

      <!-- djangotemplates/example/templates/data.html -->
      
      {% extends 'base.html' %}
      {% load humanize %} <!-- Add this-->
      
      <!DOCTYPE html>
      <html>
        <head>
          <meta charset="utf-8">
          <title></title>
        </head>
        <body>
          {% block pagecontent %}
          <div class="table-div">
            <table class="table">
              <thead>
                <tr>
                  <th>Celebrity Name</th>
                  <th>Net Worth</th>
                </tr>
              </thead>
              <tbody>
                {% for celebrity in data %}
                  <tr>
                    <td>{{ celebrity.name }}</td>
                    <td>$ {{ celebrity.worth | intcomma }}</td> <!--Modify this line-->
                  </tr>
                {% endfor %}
              </tbody>
            </table>
          </div>
          {% include 'partials/nav-bar.html' %}
          {% endblock %}
        </body>
      </html>
      

      When you go to http://localhost:8000/data/, you should now have a more friendly list of net worth values:

      There are many more filters included in the humanize package. Read about them here

      Collecting Static Files

      Remember we talked about collecting static files? Try the following command:

      python manage.py collectstatic
      

      You should see a prompt like the following:

      You have requested to collect static files at the destination
      location as specified in your settings:
      
            /Users/amos/projects/djangotemplates/staticfiles
      
      This will overwrite existing files!
      Are you sure you want to do this?
      
      Type 'yes' to continue, or 'no' to cancel:
      

      Go ahead and say yes.

      This command will tell Django to go through all your project folders, look for all static files and store them in one place (the static root we defined in the settings). This is very efficient especially if you are deploying your site to production.

      When you run the command collectstatic, you should see a new folder called staticfiles created in the root of your project folder. You can change this location to something else by editing the static root setting in your project’s settings.py file. To use these staticfiles, in your templates you will say load staticfiles instead of load static. Everything else is the same as with using the previous static folder.

      Conclusion

      Congratulations on reaching the end of this tutorial! By now you should have a more detailed understanding of how Django templates work. If you need deeper information, remember the docs are your friend. You can find the full code for this tutorial here. Make sure to leave any thoughts, questions or concerns in the comments below.



      Source link

      Working with Environment Variables in Vue.js


      While this tutorial has content that we believe is of great benefit to our community, we have not yet tested or
      edited it to ensure you have an error-free learning experience. It’s on our list, and we’re working on it!
      You can help us out by using the “report an issue” button at the bottom of the tutorial.

      In this post, we’ll learn how to work with distinct configurations between development and production mode for Vue.js projects that use the CLI’s webpack template.

      In a web app, we most likely have to access a backend API server through a URL. This URL can be something like http://localhost:8080/api while in development, and https://site.com/api in production when the project is deployed. Environment variables allow us for an easy way to change this URL automatically, according to the current state of the project.

      An easy way to use environment variables with Vue and the webpack template is through files with a .env extension. These files become responsible for storing information that’s specific to the environment (development, testing, production,…)

      The majority of this post applies to apps using v2.x of the Vue CLI, but environment variables are just as easy to manage in the Vue CLI v3.

      Using .env Files in Vue

      The simplest way to use .env files in Vue is to create an application that already supports environment files. Let’s use the vue-cli and the webpack template for that.

      With Node 8 or higher installed, run the following, where my-app is your app name:

      $ npx vue-cli init webpack my-app
      

      This command will create an application with several files ready for use. In this post, we’re focusing only on the environment configuration, which can be accessed in the config directory:

      Project file structure

      There are two files in the config directory: dev.env.js and prod.env.js, and you’ll also have a test.env.js file if you’ve configured tests while initiating the project. These files are used in development and production mode, or in other words, when you are running the application through the npm run dev command, the dev.env.js file is used, and when you compile the project for production with the npm run build command, the prod.env.js file is used instead.

      Let’s change the development file to:

      dev.env.js

      'use strict'
      const merge = require('webpack-merge')
      const prodEnv = require('./prod.env')
      
      module.exports = merge(prodEnv, {
        NODE_ENV: '"development"',
        ROOT_API: '"http://localhost/api"'
      })
      

      Our development environment file has an additional variable called ROOT_API, with the value http://localhost/api.

      Now let’s change the production file to:

      prod.env.js

      'use strict'
      module.exports = {
        NODE_ENV: '"production"',
        ROOT_API: '"http://www.site.com/api"'
      }
      

      Here we have the same ROOT_API variable, but with a different value, which should only be used in production mode. Note how string variables need the double quotes inside the single quotes.

      Using the Environment Files in Your Code

      After creating the ROOT_API variable, we can use it anywhere in Vue through the global process.env object:

      process.env.ROOT_API
      

      For example, open the src/components/HelloWorld.vue file and in the <script> tag add the following:

      mounted() {
        console.log(process.env.ROOT_API)
      }
      

      After running npm run dev, you will see the console.log information in the browser dev tools:

      Running the app

      If you run the npm run build command, the dist directory will be created with the application ready to be deployed to a production environment, and the variable ROOT_API will display the value http://www.site.com./api, as specified in prod.env.js.

      Thus, we can work with different variables for each different environment, using the ready-made configuration that the webpack template provides us. If you use another template, make sure you find an equivalent feature or use a library like dotenv to manage your environment variables.

      What About Vue CLI 3?

      If your app is using the new Vue CLI, you’ll want to instead have files like .env and .env.prod at the root of your project and include variables like this:

      .env

      VUE_APP_ROOT_API=http://localhost/api
      

      .env.prod

      VUE_APP_ROOT_API=http://www.site.com/api
      

      The VUE_APP_ prefix is important here, and variables without that prefix won’t be available in your app.



      Source link