Counts are Inaccurate

[unemployment]

If I remove the blog post join and count then the employees count works correctly.  If I don't the count is inaccurate.  How can  I have both counts work together?

                       

                        SELECT

`users`.`id` AS '0',

CHAR_LENGTH(`users`.`credentials`) AS '1',

CHAR_LENGTH(`users`.`specialties`) AS '2',

`companies`.`companyvideo` AS '3',

`investor_info`.`accredited` AS '4',

COUNT(`blog_posts`.`post_id`) AS '5',

COUNT(`votes`.`uid`) AS '6',

COUNT(`employees`.`userid`) AS '7',

'' AS '8'

FROM `users`

LEFT JOIN `blog_posts`

ON `blog_posts`.`user_id` = `users`.`id`

LEFT JOIN `votes`

ON `votes`.`uid` = `users`.`id`

LEFT JOIN `investor_info`

ON `investor_info`.`uid` = `users`.`id`

LEFT JOIN `employees`

ON `employees`.`userid` = `users`.`id`

LEFT JOIN `companies`

ON `employees`.`companyid` = `companies`.`companyid`

WHERE `users`.`id` = '${uid}'

[ManiacDan]

Put a DISTINCT inside each COUNT() call:

COUNT(DISTINCT employees.userid)

[unemployment]

Thanks ManiaDan! 

 

That fixed the first issue... now I have a new one. 

 

I would like to combine these two sql selects.  I was using a union, but that doesn't really seem to merge the data sets.  Do I need to use a subquery or do I need change the way I am joining things?  I'm not sure how to handle this.  There will likely be more data added to this query too.

 

$sql[] = "

SELECT

            `users`.`id` AS '0',

            CHAR_LENGTH(`users`.`credentials`) AS '1',

            CHAR_LENGTH(`users`.`specialties`) AS '2',

            `companies`.`companyvideo` AS '3',

            `investor_info`.`accredited` AS '4',

            COUNT(DISTINCT `blog_posts`.`post_id`) AS '5',

            COUNT(DISTINCT `votes`.`uid`) AS '6',

            COUNT(DISTINCT `employees`.`userid`) AS '7'

'' AS '8',

        FROM `users`

        LEFT JOIN `blog_posts`

        ON `blog_posts`.`user_id` = `users`.`id`

        LEFT JOIN `votes`

        ON `votes`.`uid` = `users`.`id`

        LEFT JOIN `investor_info`

        ON `investor_info`.`uid` = `users`.`id`

        LEFT JOIN `employees`

        ON `employees`.`userid` = `users`.`id`

        LEFT JOIN `companies`

        ON `employees`.`companyid` = `companies`.`companyid`

        WHERE `users`.`id` = '${uid}'";

 

$sql[] = "SELECT

'' AS '0',

'' AS '1',

'' AS '2',

'' AS '3',

'' AS '4',

'' AS '5',

'' AS '6',

'' AS '7',

COUNT(`votes`.`company_id`) AS '8'

FROM `employees`

LEFT JOIN `companies`

ON `employees`.`companyid` = `companies`.`companyid`

LEFT JOIN `votes`

ON `votes`.`company_id` = `companies`.`companyid`

WHERE `employees`.`userid` = '${uid}'";

[ManiacDan]

What are you actually trying to do?  Why are you destroying the names of your columns, and why are you selecting 7 empty columns for no reason?

 

Just from glancing at these queries, you should run them separately.

[unemployment]

What are you actually trying to do?  Why are you destroying the names of your columns, and why are you selecting 7 empty columns for no reason?

 

Just from glancing at these queries, you should run them separately.

 

I'm in the process of building a gaming engine.  Essentially people will be able to earn points in my app for accomplishing certain tasks.  The goal of this query is to pull in a bunch of data and have php create an algorithm that provides each user with a score.  Ultimately, this will end up being a half dynamic and half static point system. 

 

The queries above are actually unioned using...

 

$sql = "(\n" . implode("\n)\nUNION ALL\n(\n", $sql) . "\n)";

 

But I don't know if that's the best way to do this. I'm going to need to pull in a lot more data into that query to get all the point information I'm looking for.  Maybe I need to structure this differently?

[fenway]

I don't understand what you're trying to query.

[unemployment]

I don't understand what you're trying to query.

 

Essentially,  I am making a game for entrepreneurs and investors.  The game is simple... do certain tasks in my app and build your reputation by gaining points.

 

Here is a list of some of the requirements:

 

Personal Profile completion = 200 points

Having a company video = 50 points

If the user is an investor and is accredited add 100 points

Blog Post = 50 Points

Number of votes submitted = 5 points each

Created or joined a verified company profile = 200 points

 

This helps build credibility in the community.  My problem is that I need to join so many tables to build the point system and it's terribly confusing for me.

 

Here is the current code and yes I know it's a bit of a mess and probably doesn't need a union.

 

function get_score($uid)
{	
$sql =  array();

$sql[] = "SELECT
			CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`,
			CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`,
			`companies`.`companyvideo` AS `video`,
			`investor_info`.`accredited` AS `accredited`,
			COUNT(`blog_posts`.`post_id`) AS `post_count`,
			COUNT(`votes`.`uid`) AS `votes_sent`,
			COUNT(`votes2`.`company_id`) AS `votes_received`,
			COUNT(DISTINCT `employees`.`userid`) AS `joined_company`
		 FROM `users`
		 LEFT JOIN `blog_posts` ON `blog_posts`.`user_id` = `users`.`id`
		 LEFT JOIN `votes` ON `votes`.`uid` = `users`.`id`
		 LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id`
		 LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id`
		 LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid`
		 LEFT JOIN `votes` `votes2` ON `companies`.`companyid` = `votes2`.`company_id`
		 WHERE `users`.`id` = '${uid}'

		";

$sql = "(\n" . implode("\n)\nUNION ALL\n(\n", $sql) . "\n)";

echo '<pre>', $sql, '</pre><br />';// die($sql);

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

return $row;
}

$profile_complete = 0;
$company_video = 0;
$accredited_investor = 0;
$joined_company = 0;

$info = get_score(2);

print_array($info);

if((!empty($info['cred_chars'])) && (!empty($info['spec_chars'])))
{
$profile_complete = 200;
}

if(!empty($info['video']))
{
$company_video = 50;
}

if($info['accredited'] == 1)
{
$accredited_investor = 100;
}

$blog_posts = (50 * $info['post_count']);

$votes_submitted = (5 * $info['votes_sent']);

if($info['joined_company'] > 0)
{
$joined_company = 200;
}


$score = $profile_complete + $company_video + $accredited_investor + $blog_posts + $votes_submitted + $joined_company;

echo $score;

 

MYSQL Version: 5.1.49

 

Here is the Explain:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE users const PRIMARY PRIMARY 4 const 1

1 SIMPLE blog_posts ALL NULL NULL NULL NULL 2

1 SIMPLE votes ALL NULL NULL NULL NULL 6

1 SIMPLE investor_info eq_ref uid uid 4 const 1

1 SIMPLE employees ref unique unique 4 const 1 Using index

1 SIMPLE companies eq_ref PRIMARY PRIMARY 4 db.employees.companyid 1

1 SIMPLE votes2 ALL NULL NULL NULL NULL 6

 

Things I have attempted:

A mysql union, but I don't think that makes sense because I'm not dealing with multiple rows.  I really just need one row with a bunch of data.

 

I would like to join a lot of SELECTS, but putting them together in one query is incredibly challenging.  Please give some advice.

 

 

 

[fenway]

And what is that code trying to calculate?

[unemployment]

And what is that code trying to calculate?

 

The end goal is to calculate the users reputation versus other users on the site.  I imagine a scale on user profile that will display the percentile that the user is in compared to the rest of the user base.

 

So if you are in the 90th percentile then you would have a high reputation.

[fenway]

And why the UNION -- are you actually running this once per UID?

[unemployment]

No need for the union really it was just old code.  This score will be calculated every time you visit a users profile.

[fenway]

So you're just trying to caculate each person's score.

 

But is this query supposed to calculated EVERYONE's score, or just the one person's?

[unemployment]

So you're just trying to caculate each person's score.

 

But is this query supposed to calculated EVERYONE's score, or just the one person's?

 

Essentially it is going to calculate one person score.  You view their profile, you see their score. However, that score might ultimately be compared across everyone score to determine what percentile they fall in. 

[fenway]

OK -- well, the only thing I see wrong with the EXPLAIN if that you're missing some key indexes for the JOINs.

[unemployment]

please explain... I don't have a clue what that means.  How do I go about altering these indexs and what should I do to them to improve the joins?

[fenway]

They should all say "ref" or "eq_ref" -- you need to an index for every column that you're using in ON conditions.

[unemployment]

Wow... never knew that. What is the benefit of doing the indexes for the ON condition?

[ManiacDan]

Wow... never knew that. What is the benefit of doing the indexes for the ON condition?

So that JOINs are faster.  If the columns being used in a join are indexed, the rows are matched up faster because the database doesn't have to build a mini-index manually to support the join (in a manner of speaking)

[fenway]

Of course, assuming you're LEFT or INNER JOIN-ing, you only the need the column on the "right" table indexed.