I'm trying to use Simple.Data as my ADO, but I've run into a problem trying to put together a query that joins a couple of tables, then filters the results based on values in the non-primary tables.
Scenario is a job application app (but jobs are like a specific task to be done on a given day). There are 3 relevant tables, jobs, applications and application_history. The can be many applications for each record in the jobs tables, and many application_history records for each applications. In the application_history table, there's a status column as each application gets sent, offered and finally accepted.
So I want a query that returns all the accepted applications that are for jobs in the future; i.e. where the date column in the jobs table is in the future and where there's an associated record in the application_history table where the status column is 5 (meaning accepted).
If this was plain old SQL, I'd use this query:
SELECT A.* FROM application AS A
INNER JOIN application_history AS AH ON AH.application_id = A.id
INNER JOIN job AS J ON J.id = A.job_id
WHERE AH.status_id = 3 AND J.date > date('now')
But I want to know how to achieve the same thing using Simple.Data. For bonus points, if you could start by ignoring the 'job must be in the future' step, that will help me understand what's going on.
As a reference: Simple.Data documentation especially the part about explicit joins. You should be able to do something like this:
//db is your Simple.Data Database object
db.application
.Join(db.application_history)
.On(db.application.id == db.application_history.application_id)
.Join(db.job )
.On(db.Applications.job_id == db.job.id)
.Where(db.application_history.status_id == 3 && db.job.date > DateTime.Now());
I'm not sure whether or not Simple.Data knows how to handle the Date part.