[RESOLVED] Handling result of inner join query

[NickThissen]

Hi,

I am kinda stumped on this one... I am using an Access database to store (filepaths to) pictures as well as comments people can leave on them. One picture can have multiple comments, so the tables look like this:

_____________      _____________
|Photos      |    |Comments     |
|------------|    |-------------|
|Id (PK)     |    |Id (PK)      | 
|PhotoPath   |    |PhotoId (FK) |
|Description |    |Comment      |
|____________|    |_____________|

What I need to do now is retrieve a list of all Photos along with their Comments. So I have a class Photo with a collection of Comments:

Public Class Photo
   
    Public Property Id As Integer
    Public Property PhotoPath As String
    Public Property Description As String
    
    Public Property Comments As List(Of Comment)
 
End Class
 
Public Class Comment
  
    Public Property Id As Integer
    Public Property PhotoId As Integer
    Public Property Comment As String
    
End Class

I can retrieve the records using an INNER JOIN query (right?), as such;

SELECT Photos.*, Comments.*
FROM (Photos INNER JOIN Comments ON Photos.Id = Comments.PhotoId)

From the result of that, for each row in the result I can create a new Photo instance, set its properties and put it in a List(Of Photo) for further processing (displaying). However, how do I handle the Comments? With this query I would get a lot of duplicate Photos. I'd get a row for each comment, but most of these rows will represent the same photos...

How do I handle this the best way? I have used inner join queries before, but only for a one-to-one relationship so I never had this problem before. Now when I finally need a one-to-many relationship I realize I've no idea how to handle this appropriately...

There must be some standard way of handling this?


When I previously handled cases like this I didn't use an inner join, I just retrieved all Photos first, then iterate through them and run another query to retrieve the Comments that belong to that Photo (SELECT * FROM Comments WHERE PhotoId = ?). That works fine, but when the tables are getting larger it is getting noticeably slower. So I want to do it in a single query, I definitely want to avoid running hundreds of queries if I can

Thanks!

[jmcilhinney]

If you want to create two different types of objects then you wouldn't use a join. You'd execute two separate queries. The reason that your original code was so slow was because you were querying the Comments table over and over again. Just query it once and get all the data. You've got a various options:

Public Function GetPhotosWithComments() As List(Of Photo)
    Dim photoTable As New DataTable
    Dim commentsTable As New DataTable
 
    Using connection As New OleDbConnection("connection string here"),
          photoCommand As New OleDbCommand("SELECT * FROM Photos", connection),
          commentsCommand As New OleDbCommand("SELECT * FROM Comments", connection)
        connection.Open()
 
        Using photoReader = photoCommand.ExecuteReader()
            photoTable.Load(photoReader)
        End Using
 
        Using commentsReader = commentsCommand.ExecuteReader()
            commentsTable.Load(commentsReader)
        End Using
    End Using
 
    Dim photoList As New List(Of Photo)
    Dim photosById As New Dictionary(Of Integer, Photo)
 
    For Each row In photoTable.AsEnumerable()
        Dim photo As New Photo With {.Id = row.Field(Of Integer)("Id"),
                                     .PhotoPath = row.Field(Of String)("PhotoPath"),
                                     .Description = row.Field(Of String)("Description")}
 
        photoList.Add(photo)
        photosById.Add(photo.Id, photo)
    Next
 
    For Each row In commentsTable.AsEnumerable()
        Dim comment As New Comment With {.Id = row.Field(Of Integer)("Id"),
                                         .PhotoId = row.Field(Of Integer)("PhotoId"),
                                         .Comment = row.Field(Of String)("Comment")}
        Dim photo = photosById(comment.PhotoId)
 
        photo.Comments.Add(comment)
    Next
 
    Return photoList
End Function

You could LINQ that up a lot more and do away with the Dictionary:

Public Function GetPhotosWithComments() As List(Of Photo)
    Dim photoTable As New DataTable
    Dim commentsTable As New DataTable
 
    Using connection As New OleDbConnection("connection string here"),
          photoCommand As New OleDbCommand("SELECT * FROM Photos", connection),
          commentsCommand As New OleDbCommand("SELECT * FROM Comments", connection)
        connection.Open()
 
        Using photoReader = photoCommand.ExecuteReader()
            photoTable.Load(photoReader)
        End Using
 
        Using commentsReader = commentsCommand.ExecuteReader()
            commentsTable.Load(commentsReader)
        End Using
    End Using
 
    Dim photoList = (From row In photoTable.AsEnumerable()
                     Select New Photo With {.Id = row.Field(Of Integer)("Id"),
                                            .PhotoPath = row.Field(Of String)("PhotoPath"),
                                            .Description = row.Field(Of String)("Description")}).ToList()
 
    For Each photo In photoList
        photo.Comments.AddRange(From row In commentsTable.AsEnumerable()
                                Where row.Field(Of Integer)("PhotoId") = photo.Id
                                Select New Comment With {.Id = row.Field(Of Integer)("Id"),
                                                         .PhotoId = row.Field(Of Integer)("PhotoId"),
                                                         .Comment = row.Field(Of String)("Comment")})
    Next
 
    Return photoList
End Function

Like I said, you have various options and those are just two of them. Whatever variation you choose to use though, you should be performing exactly two queries. Query the tables separately and query each only once.

[jmcilhinney]

By the way, here's the "proper" implementation of your Photo class:

Public Class Photo
 
    Private _comments As New List(Of Comment)
 
    Public Property Id As Integer
    Public Property PhotoPath As String
    Public Property Description As String
 
    Public ReadOnly Property Comments As List(Of Comment)
        Get
            Return Me._comments
        End Get
    End Property
 
End Class

Properties that expose collections should pretty much always be read-only.

You could conceivably add a corresponding Photo property to your Comment class, if there was ever going to be a need to go back that way.

[NickThissen]

Ok, for some reason I think I could do it in one query, so I never even considered loading all the comments in one go and dividing them up later. That makes sense, thanks.

As for the read-only property, I actually have that, this was just a quick example of the classes in question, I didn't want to post all of them cause that would include alot of irrelevant information, and I was too lazy to type out the 'full' property (hence the use of auto properties).

I'm going to try this soon, but I'm pretty sure it'll work just fine, thanks!

[jmcilhinney]

You could actually do it with one query, but you'd then be retrieving and discarding a lot of the Photo data repeatedly.

If you weren't using Access, another variation would to use a single DbCommand that contained two queries. You could then call ExecuteReader once and your DataReader would contain both result sets, or you could use a DataAdapter and call Fill once to populate two DataTables in one DataSet. You could add a DataRelation to that DataSet and use that to get all the Comments rows related to a particular Photos row.

[NickThissen]

Sounds good, unfortunately this is for a website and as far as I know it only supports Access for free, you have to pay extra for a MsSQL server and I'm not doing that unless I have a very good reason. But I'll keep it in mind for other projects, thanks.