Hot File

Using JOINs in MongoDB NoSQL Databases in php

View: 291    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: Php&mySql   Fields: Other

0 point/1 review File has been tested

One of the biggest differences between SQL and NoSQL databases is JOIN. In relational databases, the SQL JOIN clause allows you to combine rows from two or more tables using a common field between them

Introduction

One of the biggest differences between SQL and NoSQL databases is JOIN. In relational databases, the SQL JOIN clause allows you to combine rows from two or more tables using a common field between them. For example, if you have tables ofbooks and publishers, you can write SQL commands such as:

SELECT book.title, publisher.name
FROM book
LEFT JOIN book.publisher_id ON publisher.id;

In other words, the book table has a publisher_id field which references the id field in the publisher table.

This is practical, since a single publisher could offer thousands of books. If we ever need to update a publisher’s details, we can change a single record. Data redundancy is minimized, since we don’t need to repeat the publisher information for every book. The technique is known as normalization.

SQL databases offer a range of normalization and constraint features to ensure relationships are maintained.

NoSQL == No JOIN?

Document-oriented databases such as MongoDB are designed to store denormalized data. Ideally, there should be no relationship between collections. If the same data is required in two or more documents, it must be repeated.

This can be frustrating, since there are few situations where you neverneed relational data. Fortunately, MongoDB 3.2 introduces a new$lookup operator which can perform a LEFT-OUTER-JOIN-like operation on two or more collections. But there’s a catch …

MongoDB Aggregation

$lookup is only permitted in aggregation operations. Think of these as a pipeline of operators which query, filter and group a result. The output of one operator is used as the input for the next.

Aggregation is more difficult to understand than simpler find queries and will generally run slower. However, they are powerful and an invaluable option for complex search operations.

Aggregation is best explained with an example. Presume we’re creating a social media platform with a user collection. It stores every user’s details in separate documents. For example:

{
  "_id": ObjectID("45b83bda421238c76f5c1969"),
  "name": "User One",
  "email: "userone@email.com",
  "country": "UK",
  "dob": ISODate("1999-09-13T00:00:00.000Z")
}

We can add as many fields as necessary, but all MongoDB documents require an _id field which has a unique value. The_id is similar to an SQL primary key, and will be inserted automatically if necessary.

Our social network now requires a post collection, which stores numerous insightful updates from users. The documents store the text, date, a rating and a reference to the user who wrote it in a user_id field:

{
  "_id": ObjectID("17c9812acff9ac0bba018cc1"),
  "user_id": ObjectID("45b83bda421238c76f5c1969"),
  "date: ISODate("2016-09-05T03:05:00.123Z"),
  "text": "My life story so far",
  "status": "important"
}

We now want to show the last twenty posts with an “important” rating from all users in reverse chronological order. Each returned document should contain the text, the time of the post and the associated user’s name and country.

The MongoDB aggregate query is passed an array of pipeline operators which define each operation in order. First, we need to extract all documents from the post collection which have the correct status using the $match filter:

{ "$match": { "status": "important" } }

We must now sort the matched items into reverse date order using the $sort operator:

{ "$sort": { "date": -1 } }

Since we only require twenty posts, we can apply a $limit stage so MongoDB only needs to process data we want:

{ "$limit": 20 }

We can now join data from the user collection using the new $lookup operator. It requires an object with four parameters:

  • localField: the lookup field in the input document
  • from: the collection to join
  • foreignField: the field to lookup in the from collection
  • as: the name of the output field.

Our operator is therefore:

{ "$lookup": {
  "localField": "user_id",
  "from": "user",
  "foreignField": "_id",
  "as": "userinfo"
} }

This will create a new field in our output named userinfo. It contains an array where each value is the matching the userdocument:

"userinfo": [
  { "name": "User One", ... }
]

We have a one-to-one relationship between the post.user_id and user._id, since a post can only have one author. Therefore, our userinfo array will only ever contain one item. We can use the $unwind operator to deconstruct it into a sub-document:

{ "$unwind": "$userinfo" }

The output will now be converted to a more practical format which can have further operators applied:

"userinfo": {
  "name": "User One",
  "email: "userone@email.com",
  …
}

Finally, we can return the text, the time of the post, the user’s name and country using a $project stage in the pipeline:

{ "$project": {
  "text": 1,
  "date": 1,
  "userinfo.name": 1,
  "userinfo.country": 1
} }

Putting It All Together

Our final aggregate query matches posts, sorts into order, limits to the latest twenty items, joins user data, flattens the user array and returns necessary fields only. The full command:

db.post.aggregate([
  { "$match": { "status": "important" } },
  { "$sort": { "date": -1 } },
  { "$limit": 20 },
  { "$lookup": {
    "localField": "user_id",
    "from": "user",
    "foreignField": "_id",
    "as": "userinfo"
  } },
  { "$unwind": "$userinfo" },
  { "$project": {
    "text": 1,
    "date": 1,
    "userinfo.name": 1,
    "userinfo.country": 1
  } }
]);

The result is a collection of up to twenty documents. For example:

[
  {
    "text": "The latest post",
    "date: ISODate("2016-09-27T00:00:00.000Z"),
    "userinfo": {
      "name": "User One",
      "country": "UK"
    }
  },
  {
    "text": "Another post",
    "date: ISODate("2016-09-26T00:00:00.000Z"),
    "userinfo": {
      "name": "User One",
      "country": "UK"
    }
  }
  ...
]

Great! I Can Finally Switch to NoSQL!

MongoDB $lookup is useful and powerful, but even this basic example requires a complex aggregate query. It’s not a substitute for the more powerful JOIN clause offered in SQL. Neither does MongoDB offer constraints; if a user document is deleted, orphan post documents would remain.

Ideally, the $lookup operator should be required infrequently. If you need it a lot, you’re possibly using the wrong data store …

Using JOINs in MongoDB NoSQL Databases in php

Using JOINs in MongoDB NoSQL Databases in php Posted on 03-10-2016  One of the biggest differences between SQL and NoSQL databases is JOIN. In relational databases, the SQL JOIN clause allows you to combine rows from two or more tables using a common field between them 5/10 291

Comment:

To comment you must be logged in members.

Files with category

  • How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    View: 3931    Download: 0   Comment: 0   Author: none  

    How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    Category: Php&mySql
    Fields: Other

    2.5/2 review
    With the explosion of machine learning services in recent years, it has become easier than ever for developers to create “smart apps”. In this article, I’ll introduce you to Microsoft’s offering for providing machine-learning capabilities to apps.

  • How to MySqli Tutorial PHP MySqli Extension

    View: 356    Download: 0   Comment: 0   Author: none  

    How to MySqli Tutorial PHP MySqli Extension

    Category: Php&mySql
    Fields: Other

    0/0 review
    PHP provides three api to connect mysql Database.

  • Make Laravel Artisan Commands

    View: 329    Download: 0   Comment: 0   Author: none  

    Make Laravel Artisan Commands

    Category: Php&mySql
    Fields: Other

    0/0 review
    Artisan is the command line tool used in Laravel framework. It offers a bunch of useful command that can help you develop application quickly. Apart from Artisan available commands, you can create your own custom commands to improve your workflow.

  • Check if a Number is a Power of 2

    View: 310    Download: 0   Comment: 0   Author: none  

    Check if a Number is a Power of 2

    Category: Php&mySql
    Fields: Other

    2.25/2 review
    How to check if a number is a power of 2. To understand this question, let’s take some example.

  • Concatenate columns in MySql

    View: 372    Download: 0   Comment: 0   Author: none  

    Concatenate columns in MySql

    Category: Php&mySql
    Fields: Other

    0/1 review
    Artisan is the command line tool used in Laravel framework. It offers a bunch of useful command that can help you develop application quickly. Apart from Artisan available commands, you can create your own custom commands to improve your workflow

  • How to Query NULL Value in MySql

    View: 308    Download: 0   Comment: 0   Author: none  

    How to Query NULL Value in MySql

    Category: Php&mySql
    Fields: Other

    5/1 review
    Misunderstanding NULL is common mistake beginners do while writing MySql query. While quering in MySql they compare column name with NULL. In MySql NULL is nothing or in simple word it isUnknown Value so if you use comparison operator for NULL values...

  • How to Abstract Class in PHP

    View: 346    Download: 0   Comment: 0   Author: none  

    How to Abstract Class in PHP

    Category: Php&mySql
    Fields: Other

    0/0 review
    What is an abstract class in PHP and when to use an abstract class in your application. In this tutorial, we’ll learn about abstract class and their implementation.

  • Use Enums in Rails for Mapped Values

    View: 308    Download: 0   Comment: 0   Author: none  

    Use Enums in Rails for Mapped Values

    Category: Php&mySql
    Fields: Other

    2.5/2 review
    When I worked in a call center, we used to mark cases with different statuses. This allowed upper management to get a handle on where cases stood, what the bottlenecks were and flow of calls. Thankfully it has been a long time since I worked in a...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd