Asked  7 Months ago    Answers:  5   Viewed   37 times

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?

 Answers

51

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

Wednesday, March 31, 2021
 
Litty
answered 7 Months ago
52

There's a conflict between the PHP that is used by Apache and the PHP that is linked to the command line. (It happens more often that it should to be honest).

What is typically done is:

which php

This tells you which php will be expecuted when running in the command line. e.g. /usr/bin/php

mv /usr/bin/php /usr/bin/php.old

Then link or copy the correct PHP version to an executable path:

ln -s /path/to/php/bin/php /usr/bin/php

or the following should also work.

cp /path/to/php/bin/php /usr/bin/php

Also suggested if you want to be able to manually install mods:

ln -s /path/to/php/bin/phpize /usr/bin/phpize
ln -s /path/to/php/bin/php-config /usr/bin/php-config

This way your CLI will match your webserver.

Update:

If as noted in this answer if you are using Ubuntu with multiple alternative installations of PHP you can do:

sudo update-alternatives --set php /usr/bin/php<version>
sudo update-alternatives --set phar /usr/bin/phar<version>
sudo update-alternatives --set phar.phar /usr/bin/phar.phar<version> 
sudo update-alternatives --set phpize /usr/bin/phpize<version> 
sudo update-alternatives --set php-config /usr/bin/php-config<version>
Wednesday, March 31, 2021
 
superfell
answered 7 Months ago
58

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'
//   )
// )
Wednesday, March 31, 2021
 
Crashthatch
answered 7 Months ago
66

try an other version much easier. It is based in PHP 7 and is ready to run phpunit tests in one comand dunit

$ dstop $ git checkout builder $ . ./aliases.sh $ dup $ dunit

Wednesday, March 31, 2021
 
the_e
answered 7 Months ago
84

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');
}
Wednesday, March 31, 2021
 
Puneet
answered 7 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :