How to join three table by laravel eloquent model

Question 1

I have three table

Articles table

 id
 title
 body
 categories_id
 user_id

Categories table

  id
  category_name

User table

 id
 user_name
 user_type

I want to show articles with their category name instead of category_id and user_name instead of user_id I try like these query It is work!

$articles =DB::table('articles')
                ->join('categories', 'articles.id', '=', 'categories.id')
                ->join('users', 'users.id', '=', 'articles.user_id')
                ->select('articles.id','articles.title','articles.body','users.username', 'category.name')
                ->get();

But I want to do by Eloquent way. Please, how could I do?

Question 2

With Eloquent its very easy to retrieve relational data. Checkout the following example with your scenario in Laravel 5.

We have three models:

1) Article (belongs to user and category)

2) Category (has many articles)

3) User (has many articles)

1) Article.php

<?php

namespace AppModels;
 use Eloquent;

class Article extends Eloquent{

    protected $table = 'articles';

    public function user()
    {
        return $this->belongsTo('AppModelsUser');
    }

    public function category()
    {
        return $this->belongsTo('AppModelsCategory');
    }

}

2) Category.php

<?php

namespace AppModels;

use Eloquent;

class Category extends Eloquent
{
    protected $table = "categories";

    public function articles()
    {
        return $this->hasMany('AppModelsArticle');
    }

}

3) User.php

<?php

namespace AppModels;
use Eloquent;

class User extends Eloquent
{
    protected $table = 'users';

    public function articles()
    {
        return $this->hasMany('AppModelsArticle');
    }

}

You need to understand your database relation and setup in models. User has many articles. Category has many articles. Articles belong to user and category. Once you setup the relationships in Laravel, it becomes easy to retrieve the related information.

For example, if you want to retrieve an article by using the user and category, you would need to write:

$article = AppModelsArticle::with(['user','category'])->first();

and you can use this like so:

//retrieve user name 
$article->user->user_name  

//retrieve category name 
$article->category->category_name

In another case, you might need to retrieve all the articles within a category, or retrieve all of a specific user`s articles. You can write it like this:

$categories = AppModelsCategory::with('articles')->get();

$users = AppModelsCategory::with('users')->get();

You can learn more at http://laravel.com/docs/5.0/eloquent

Question 3

Laravel supports aliases on tables and columns with AS. Try

$users = DB::table('really_long_table_name AS t')
           ->select('t.id AS uid')
           ->get();

Let's see it in action with an awesome tinker tool

$ php artisan tinker
[1] > Schema::create('really_long_table_name', function($table) {$table->increments('id');});
// NULL
[2] > DB::table('really_long_table_name')->insert(['id' => null]);
// true
[3] > DB::table('really_long_table_name AS t')->select('t.id AS uid')->get();
// array(
//   0 => object(stdClass)(
//     'uid' => '1'
//   )
// )

Question 4

Tools like XAMP, MAMP or WAMP have their own PHP and MySQL binaries and do not use a potential globally installed version.
I guess you are using MySQL installed with XAMP, so the socket is not the default one that you could search in your system but the one in XAMP.

You have two options:

Update your global PHP's php.ini to set the pdo_mysql.default_socket or mysqli.default_socket to the MySQL socket in your XAMP installation in order to your global PHP binary will be able to connect the your XAMP's MySQL version.
Use the XAMP's PHP binary (recommanded).

The recommanded option is the second one, because the XAMP's PHP binary is already configured to use the right MySQL socket and does not need to update any configuration file.

I explain below how to find the MySQL socket and how to locate the PHP binary directory.

Find the MySQL socket path:

Write the following code in you Web server directory (usually www/, htdocs/ or public_html/)
```
<?php
//phpinfo.php
phpinfo();
```
Open the URL http://localhost/phpinfo.php in your Web browser (adapt the host name if required).
According to the PHP extension you use to connect to the datase search for either pdo_mysql.default_socket or mysqli.default_socket and you'll find the path to the socket file.

See phpinfo().

Find the PHP binary directory:

Write the following code in your Web server directory
```
<?php
//bindir.php
echo PHP_BINDIR;
```
Open the URL http://localhost/bindir.php in your Web browser.
The path to the directory hosting the PHP binary is written.

Thanks to this answer for the information.
See the documentation about pre-defined constants.

In your case the directory hosting PHP is /Applications/XAMPP/bin/ so you should run the command:

/Applications/XAMPP/bin/php artisan migrate

Question 5

I would use Eager Loading.

public function index()
{
    $user = User::with([
        'campuses' => function($query) {
           $query->select(['id', 'CampusName']);     
        }, 
        'tickets'
      ])->where('id', Auth::id())->first();

    $campuses = $user->campuses->pluck('CampusName');
    $tickets = Ticket::all()->where('AssignedTo', $user->id);

    return view('home')->with([
       'user' => $user,
       'campuses'=>$user->campuses->pluck('CampusName'),
       'tickets'=>$user->tickets]);
}

EDIT

You need to update your User model.

public function campuses()
{
    return $this->hasMany(Campus::class, 'TechID');
}

public function tickets()
{
    return $this->hasMany(Ticket::class, 'AssignedTo');
}

Question 6

Since you're referencing an id you need to make the foreign key column unsigned. As ids are by default unsigned (non-negative).

So do this for all your foreign keys:

$table->integer('product_id')->unsigned();

How to join three table by laravel eloquent model

Articles table

Categories table

User table

Answers

EDIT

php,mysql,laravel