I trying to figure out how can I build a personal gallery for each user. It already records each uploaded photo with the original UserId.
So I was thinking that the only page that might be changed is the default.cshtml in order to select not all the albuns and photos from the database but just the photos that the logged user uploaded. It seemed not to be so difficult, however I´m stuck here,
because I don´t know how to do it using two tables (with join function).
Thanks a lot for any suggestion!
@{
WebSecurity.RequireAuthenticatedUser();
var
galleryId = UrlData[0].AsInt();
var
db = Database.Open("PhotoGallery");
Page.Title ="Photo
Gallery"; var
galleries = db.Query(@"SELECT
Galleries.Id, Galleries.Name, COUNT(Photos.Id) AS PhotoCount
FROM Galleries LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId
GROUP BY Galleries.Id, Galleries.Name ").ToList();
I tried very hard to understand how it works but I still can´t write de correct code.
Lets see...
I changed two things...
var user = db.QuerySingle("SELECT
UserId, DisplayName, Bio FROM UserProfiles WHERE UserId = @0",
WebSecurity.CurrentUserId);
var galleries = db.Query(@"SELECT from Galleries.Id, Galleries.Name, where UserId = user, COUNT(Photos.Id) AS PhotoCount FROM Galleries, LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId GROUP BY Galleries.Id, Galleries.Name").ToList(); }
The error is in the var galleries because the "var user" is ok.
[ Token line number = 2,Token line offset = 62,Token in error = user ]
What I need is to select just the photos that each member uploaded. In the original template any member can see all the photos and I need to change to a personal gallery, which just who uploaded the photo can see it.
var galleries = db.Query(@"SELECT from Galleries.Id, Galleries.Name, where UserId = @0, COUNT(Photos.Id) AS PhotoCount FROM Galleries, LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId GROUP BY Galleries.Id, Galleries.Name", user).ToList();
Marked as answer by mclazarini on Apr 17, 2012 02:02 AM
mclazarini
Member
63 Points
57 Posts
Photo Gallery - User ID
Apr 10, 2012 02:42 AM|LINK
Hi!
I trying to figure out how can I build a personal gallery for each user. It already records each uploaded photo with the original UserId.
So I was thinking that the only page that might be changed is the default.cshtml in order to select not all the albuns and photos from the database but just the photos that the logged user uploaded. It seemed not to be so difficult, however I´m stuck here, because I don´t know how to do it using two tables (with join function).
Thanks a lot for any suggestion!
@{
WebSecurity.RequireAuthenticatedUser();
var galleryId = UrlData[0].AsInt();
var db = Database.Open("PhotoGallery");
Page.Title ="Photo Gallery";
var galleries = db.Query(@"SELECT Galleries.Id, Galleries.Name, COUNT(Photos.Id) AS PhotoCount
FROM Galleries LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId
GROUP BY Galleries.Id, Galleries.Name ").ToList();
}
SonicMan
Participant
1472 Points
228 Posts
Re: Photo Gallery - User ID
Apr 12, 2012 07:21 AM|LINK
Hi
I can't know your db structure.
But I think you can get how to use join in w3cschool:
http://www.w3schools.com/sql/sql_join.asp
http://www.tizag.com/sqlTutorial/sqljoin.php
Dino He - MS...
Star
8068 Points
1023 Posts
Microsoft
Re: Photo Gallery - User ID
Apr 12, 2012 08:42 AM|LINK
Hi
I think you know how to use where clause
Lets compare them:
WHERE
SELECT Patients.Name, Doctors.Specialty
FROM Patients, Doctors
WHERE Patients.DoctorID = Doctors.DoctorID
JOIN
SELECT Patients.Name, Doctors.Specialty
FROM Patients
INNER JOIN Doctors ON Patients.DoctorID = Doctors.DoctorID
Hope it helpful.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
mclazarini
Member
63 Points
57 Posts
Re: Photo Gallery - User ID
Apr 13, 2012 12:30 PM|LINK
Sonic and Dino....
Thanks a lot for the help. I´ll check it out!!!
mclazarini
Member
63 Points
57 Posts
Re: Photo Gallery - User ID
Apr 13, 2012 07:37 PM|LINK
I tried very hard to understand how it works but I still can´t write de correct code.
Lets see...
I changed two things...
var user = db.QuerySingle("SELECT UserId, DisplayName, Bio FROM UserProfiles WHERE UserId = @0", WebSecurity.CurrentUserId);
var galleries = db.Query(@"SELECT from Galleries.Id, Galleries.Name, where UserId = user, COUNT(Photos.Id) AS PhotoCount FROM Galleries, LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId GROUP BY Galleries.Id, Galleries.Name").ToList(); }
The error is in the var galleries because the "var user" is ok.
[ Token line number = 2,Token line offset = 62,Token in error = user ]
What I need is to select just the photos that each member uploaded. In the original template any member can see all the photos and I need to change to a personal gallery, which just who uploaded the photo can see it.
The error
Thanks a lot,
Mclazarini
GmGregori
Contributor
5564 Points
749 Posts
Re: Photo Gallery - User ID
Apr 13, 2012 09:12 PM|LINK
Try with:
var galleries = db.Query(@"SELECT from Galleries.Id, Galleries.Name, where UserId = @0, COUNT(Photos.Id) AS PhotoCount FROM Galleries, LEFT OUTER JOIN Photos ON Galleries.Id = Photos.GalleryId GROUP BY Galleries.Id, Galleries.Name", user).ToList();
mclazarini
Member
63 Points
57 Posts
Re: Photo Gallery - User ID
Apr 13, 2012 10:18 PM|LINK
Thanks, but it didn´t work...
[ Token line number = 1,Token line offset = 8,Token in error = from ]
GmGregori
Contributor
5564 Points
749 Posts
Re: Photo Gallery - User ID
Apr 14, 2012 09:08 AM|LINK
I give only a superficial check to your query in my first answer.
I'm not sure of your goal, but the following query works:
var galleries = db.Query("SELECT Galleries.Id, Galleries.Name, COUNT(Photos.Id) AS PhotoCount " + "FROM Galleries LEFT JOIN Photos ON Galleries.Id = Photos.GalleryId " + "WHERE Photos.UserId = @0 GROUP BY Galleries.Id, Galleries.Name", user);mclazarini
Member
63 Points
57 Posts
Re: Photo Gallery - User ID
Apr 16, 2012 11:13 PM|LINK
Ohh tnanks for the advice.
mclazarini
Member
63 Points
57 Posts
Re: Photo Gallery - User ID
Apr 17, 2012 01:42 AM|LINK
...