Loading a subset of data from one table based on conditions of parents in a different table

dhutton@creativeone.com

What I need to do is build a datatable that's editable that selects a list of attendees who attended a list of seminars. Consider the below:

        public async Task<List<AttendeesModel>> GetAttendees()
        {
            var seminars = await (from b in _context.seminars orderby b.seminardateadded select b)
                .Where(b => b.name.Contains("dave")).ToListAsync();
            List<AttendeesModel> withinRange = new List<AttendeesModel>();

            foreach (SeminarsModel seminar in seminars)
            {
                    List<AttendeesModel> attendees = await (from b in _context.attendees orderby b.attendeedateadded select b)
                        .Where(b => b.seminarid == seminar.id).ToListAsync();
                    foreach (AttendeesModel attendee in attendees) 
                        { withinRange.Add(attendee); }
            }``
            return withinRange;
        }

I changed the above to be more concise and more readable in the forum context. It returns a list of attendees that match the criteria / conditions.

I need to do the same as above in a way that datatables / editor is happy with. I'm not sure if I want an mjoin, but basically I have to fetch a subset of seminars, then grab all of the attendees who contain a reference to one of those seminars. I'm not sure how to translate this into server side .NET code for editor to be able to work with. I've written query-based where clauses and know you can chain them, I'm just not sure how to incorporate the logic I have above while building the response for editor to chew on. ie.

                    .Where(q => q.Where("seminarstatus", "(SELECT seminarstatus FROM seminars WHERE seminarstatus = 'Completed')", "IN", false))

Typical block in controller I'm using for datatables views:

                   using (var db = new Database(dbType, dbConnection))
                    {
                        var response = new Editor(db, "attendees", "id")
                            .Where("id", 42000, ">")
                            .Model<AttendeesModel>()
                            .TryCatch(false)
                            .Process(Request)
                            .Data();
                        return Json(response);
                    }
                }

dhutton@creativeone.com

I translated the above code into my sql query below and confirmed it's giving me back the results I want:

SELECT attendees.*
FROM attendees
LEFT JOIN seminars
ON attendees.seminarid = seminars.id
WHERE seminars.seminarstatus = 'Upcoming'
order by attendeedateadded desc

Here's the new editor instance I'm building. Currently this kicks back an error that id is ambiguous so my syntax is wrong but it's the end of the day. Can someone help?

                var response = new Editor(db, "attendees", "id")
                    .LeftJoin("seminars", "attendees.seminarid", "=", "seminars.id")
                    .Where(q => q.Where("seminarstatus", "(SELECT seminars.seminarstatus FROM seminars WHERE lower(seminarstatus) LIKE '%Upcoming%')", "IN", false))
                    .Model<AttendeesModel>()
                    .TryCatch(false)
                    .Process(Request)
                    .Data();
                return Json(response);

allan

In the Editor code you are doing a sub-select, but the SQL you constructed isn't doing any of that - its much simpler and could be modeled in Editor with:

var response = new Editor(db, "attendees", "id")
    .LeftJoin("seminars", "attendees.seminarid", "=", "seminars.id")
    .Where("seminars.seminarstatus", "Upcoming")
    .Model<AttendeesModel>()
    .TryCatch(false)
    .Process(Request)
    .Data();
return Json(response);

Assuming that seminars.seminarstatus is in your AttendeesModel?

Allan

dhutton@creativeone.com

seminars.seminarstatus is in the seminars table and seminarstatus is the column. I wanted to return the attendees (attendeesmodel is the model for an attendee) whose associated seminar (attendees.seminarid = seminars.id) matched the criteria of seminarstatus = "Upcoming". I made the attendees my left table as some seminars don't have any attendees yet (too new) and I only want to return attendees.

dhutton@creativeone.com

Ugh, I think what's happening is that with postgres at least, once you involve multiple tables you have to prefix all of your column names with the table name or this error is thrown. I can confirm the select statement it's building (snippet below) isn't doing that and the id column it's having a problem with does exist in both tables that are involved in this join.

Is there a way to force prefixing every column name with the table name?

(below example, another_id is the one it's having a problem with)

SELECT attendees.id as \"attendees.id\", another_id as \"another_id\", agentid as \"agentid\", etc.

I suspect, based off my web search, it wants something more like:

SELECT attendees.id as \"attendees.id\", attendees.another_id as \"attendees.another_id\", attendees.agentid as \"attendees.agentid\", etc.

If you don't do that and a column exists by the same name in both tables, you get this ambiguous exception thrown

allan

Yes you can have the Editor server-side code prefix table names from the model by passing the table name for the model to the .Model<>() method.

Note that you'd need to update the client-side references (in DataTables and Editor) to these tables / columns as well. You'd also need to make sure that you include the fields for the joined table (or at least the ones you want to reference) in your server-side code.

This example might also be of some use.

Allan

dhutton@creativeone.com

That worked! Thanks Allan