Page 2 of 2

Posted: Wed Jan 10, 2007 9:30 am
by Grim...
This could be what you're after, and if it's not, it should give you an idea of how to get what you want...

Code: Select all

SELECT object.id AS object, 
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)

Code: Select all

object 	num_choice 	total_rating 	final_score
1       	4           	10             	14

Posted: Wed Jan 10, 2007 9:40 am
by Yaniv
Grim... wrote:So it gets one 'point' for each user that chose it, plus whatever ratings it has?
It gets raised a point if the user rated +1 and loses a point if the user rated -1.
The result must be for each object per each choice.


About stored procedures, I can't use that because I must leave the project DB independent.

Posted: Wed Jan 10, 2007 9:41 am
by Grim...
So how about the SQL above (which I suspect you missed because of the new page)?

Posted: Wed Jan 10, 2007 9:49 am
by Yaniv
Grim... wrote:This could be what you're after, and if it's not, it should give you an idea of how to get what you want...

Code: Select all

SELECT object.id AS object, 
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)

Code: Select all

object 	num_choice 	total_rating 	final_score
1       	4           	10             	14
That won't work because it groups them by object.id .
I need each object to list all choices and each choice to have a different result for each object.

Posted: Wed Jan 10, 2007 10:11 am
by Grim...
I can't see how choice links to object - there seems to be no common id's between them.

Posted: Wed Jan 10, 2007 10:21 am
by kingconnections
I would like to ask a question real quick.

On this section of code (below), are you opening database connections and retrieving info while in those loops?

Code: Select all

objects = (Retrieve list of Objects from DB) 
foreach(objects) 
{ 
    users = (retrieve list of users who are linked to this object) 
    foreach(users) 
   { 
      profile = (retrieve list of user choices for this user) 
      (count each choice to a large list of choices statistics; each choice +1 if a user chose it) 
   } 
} 

(Send the choices statistics back to the database)

The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.

so for example

Code: Select all

// just examples
while ($row = mssql_fetch_assoc($result)){
	$in_website[]=strtoupper($row['server']);
}

while ($row = mssql_fetch_assoc($result)){
	$in_EPO[]=strtoupper($row['NodeName']);
	$server =strtoupper($row['NodeName']);
	$Epo_Data["$server"]=$row['LastUpdate'];
}


then ouput 

foreach ($server)

{
echo  $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.

Posted: Wed Jan 10, 2007 10:28 am
by Yaniv
Grim... wrote:I can't see how choice links to object - there seems to be no common id's between them.
They are linked indirectly by object_user and user_choice

Posted: Wed Jan 10, 2007 10:31 am
by Yaniv
kingconnections wrote:I would like to ask a question real quick.

On this section of code (below), are you opening database connections and retrieving info while in those loops?

Code: Select all

objects = (Retrieve list of Objects from DB) 
foreach(objects) 
{ 
    users = (retrieve list of users who are linked to this object) 
    foreach(users) 
   { 
      profile = (retrieve list of user choices for this user) 
      (count each choice to a large list of choices statistics; each choice +1 if a user chose it) 
   } 
} 

(Send the choices statistics back to the database)

The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.

so for example

Code: Select all

// just examples
while ($row = mssql_fetch_assoc($result)){
	$in_website[]=strtoupper($row['server']);
}

while ($row = mssql_fetch_assoc($result)){
	$in_EPO[]=strtoupper($row['NodeName']);
	$server =strtoupper($row['NodeName']);
	$Epo_Data["$server"]=$row['LastUpdate'];
}


then ouput 

foreach ($server)

{
echo  $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.

Yeah I do database access inside those loops. Perhaps that is the problem.

Posted: Wed Jan 10, 2007 10:44 am
by kingconnections
Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....

Posted: Wed Jan 10, 2007 10:47 am
by Yaniv
kingconnections wrote:Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
I will have to give this thing a shot. Thanks for the direction

Posted: Thu Jan 11, 2007 6:21 am
by Yaniv
It turned out rather simple to do this for a single object.

Code: Select all

SELECT *, SUM(object_user.rating)
FROM user_choice, object_user
WHERE user_choice.user_id=object_user.user_id
AND object_user.object_id=1
GROUP BY user_choice.choice_id
ORDER BY user_choice.choice_id
Yet I want to find a way to do the same thing for all object together to save database access time. In this method I still need to call this query for each object I have on the system.