Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

I've been scratching my head over this one, could you do an sql dump and post it here so we could see
all fields in all tables you have?

0

Hi!

My badge table,"badges"

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`id`)

My user table, "users"

`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(75) COLLATE utf8_unicode_ci NOT NULL, 
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL, 
PRIMARY KEY (`id`)

And the pivot table between them, "badge_user"

`id` int(11) NOT NULL AUTO_INCREMENT,
`badge_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
`achieved_at` int(11) NOT NULL,
`accuracy` tinyint(4) NOT NULL DEFAULT '0',
`state` char(1) COLLATE utf8_unicode_ci NOT NULL,
`comment` varchar(140) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)

My model "User" has defined the following relation,

public function badges()
{
return $this->belongsToMany('Badge', 'badge_user')->withPivot('created_at', 'updated_at', 'achieved_at', 'accuracy', 'state', 'comment');
}

;)

Thank you very much again!

0

I don't see where user is linked to badges, but would
a simpler table work:

ID     	NAME		MERIT	ARCHERY	   SKING	TOBACCO_SPITTING  
1	JOE BLOW         2        3          1                1   
2	SALLY MAE        1        2          5                0  
3	BILLY BOB        1        0          0               19  

SiPoX said:

Last updated 9 years ago.
0

Hi jimgwhit! ;)

Yes, the field "name" in the badge table is the name of the badge. I don't understand what you mean when you say "where user is linked to badge". ;) The relation (defined in the user model) badges() joins a user with his badges through the pivot table badge_user, doesn't it?

In fact, I can get the user's badges, and the badges of a user from a category (first example above). But I don't know how I could calculate the max and the min category of a user (I mean, in which category a user has more badges and in which less).

I hope to be able to explain myself ;) :P

Thank you very much! ;)

0

Is badge_user a table in your database, or is it a temp table being created?

0

SiPoX said: But I would get the best category of the user. I mean, the category where the user has the max number of badges. How could I execute this? I've tried several ways with no results.. :( ;)

I'm just not seeing where you are entering a number of badges. I see badge_id` int(11) NOT NULL in table badge_user.
But where are you actually entering something like
Joe Blow has 12 badges for swimming? I believe you are a couple of fields short, but without having the whole application I could be and probably am wrong -- sorry. Please, if you solve this on your own, don't leave us hanging, please show what you did, I think anyone who sees the final solution can learn from it.

0

Hi!

Yes, my table badge_user is in my database. ;) Well, I don't save how many badges a user has, due to the fact I calculate this with the SQL function count().

"Manually", I did (in my User model)

public function bestWeakest()
{
    // IDs of my categories (categories are in the table categories but I manually set them here)
$topics = array (3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

    // Variables to save the max ID and the min ID
$max = 0;
$min = 0;
	
foreach ($topics as $topic)			
{
          // Number of badges of this user by each category
      $nob = $this->achievedBadgesByTopic($topic)->count();
      
           // Save if max
           if ($nob >= $max) $max = $topic;

       // Save if min
           if ($nob <= $min) $min = $topic;
}

// Compose an array
$r = array('max' => $max, 'min' => $min);
				
// Return the array			
return $r;
}                    

Obviously, it works, but I wonder if I could do this through a SQL sentence via Laravel Eloquent functions. ;) :P

Thank you again! ;)

0

Sign in to participate in this thread!

Eventy

Your banner here too?

SiPoX sipox Joined 5 Dec 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.