Displaying the data in a different structure/format, requires a different query?

[eluser]kyleect[/eluser]
I'm currently writing a resume generator for an app I'm building however I'm having trouble in the design phase. The real issue comes down having flexibility in how I sort and list the job history. A simple reverse chronological order is easy but what about reverse chronological order, grouping by company name and the company name is a header with those jobs listed under each header. Here are the issues I'm facing so far.

How do I list the company headers in chronological order? I can't put dates in the companies database because it needs to support multiple users.

Can I do all this in a single query? I know I can query for the company names and query for jobs for each company but I'm wondering if I can avoid this.

Code:

companies
* id
* company_name

jobs
* id
* user_id
* company_id
* job_title
* start_date
* end_date

[eluser]jedd[/eluser]
Howdi,

I like how you've given half a schema, and not explained it / your rationale - and assumed we can work out what you are doing. It makes me feel super smart. Unfortunately I can't work out what you're doing .. so now I feel extra dumb. And I blame you for that.

A simple reverse chronological order is easy but what about reverse chronological order, grouping by company name and the company name is a header with those jobs listed under each header.

This seems straightforward - just a second order by component on your select. What's your current query look like?

How do I list the company headers in chronological order? I can't put dates in the companies database because it needs to support multiple users.

But job can have dates because ... ?

Does job refer to a role, a generic activity, or a specific person's job only?

[eluser]kyleect[/eluser]
Jobs are... positions in the company? A resume would be for a single person, I'm not sure I've seen resumes that have more than one person on it. I just assumed a resume was a fairly basic and familiar item and has a familiar syntax.

The reason companies can't have a date is because many people will have worked at.. lets say Wells Fargo. Did they all work there at the same time? No. They each got hired at different times and stopped working there at different times. On a resume, this is usually represented by the jobs they list on the resume. Seriously, there is no need to be a cock.

Example:

User id=1:

Company id=2
- Job id=3

Company id=1
- Job id=2
- Job id=1

User id=2:

Company id=2:
- Job id=4

Company id=3
- Job id=5

Maybe a simpler example. I have many blog posts, each have a category ID. That is joined with a table that has an id and a category name. In my blog, instead of listing the entries in reverse chronological order, I would list the categories as headers on the page and order them according to the entries dates in the categories. The category with the most recent post listed first and so on. Then the entries sorted by dates under each category. Does that make more sense? It's exactly the same thing I'm talking about.

[eluser]jedd[/eluser]
[quote author="a cock" date="1252883574"]
This seems straightforward - just a second order by component on your select. What's your current query look like?
[/quote]

... ?


[quote author="kyleect" date="1252887095"]
Jobs are... positions in the company?
[/quote]

This is how I'd see it, but position / role suggests immutability, whereas someone occupying that - their job, say - is an ephemeral state.

That is, people:position is n:1. The reason I asked was because your schema looks broken. And you may be assuming it's perfectly suited to what you are trying to do.

You seem to touch on the nature of position : human relationship in your last paragraph there.

A resume would be for a single person, I'm not sure I've seen resumes that have more than one person on it.

I didn't see any references to a resume table in the first posting. Is this another table(s), or just the name you're giving to a logical grouping of some of your tables?

Maybe a simpler example. I have many blog posts, each have a category ID. That is joined with a table that has an id and a category name.

Whilst simpler, it may highlight a point of potential confusion.

To allow for multiple categories, you'd have a blog post table that had no understand of category ID's. You'd have a category table. And you'd have your connecting table that had blog.id and category.id in it.

I'm trying to work out what you're trying to achieve, and what entities and relationships you've set up / assumed so far. You can either spill the whole lot, or let me try to guess bits and then get annoyed when you reveal your intent is different from what I've intuited. No prizes for guessing my preference.

[eluser]jedd[/eluser]
Okay .. re-reading between the lines of your first message.

Do you want to be able to generate reports showing :
o everyone who has had held a particular job title (or job titles)
o everyone who's worked for a given company (or companies)
o people who've worked in the same company and in the same job

Will people, as they enter their raw data, be able to create job titles, or will you be providing a stock set of these (or combination).

Are skills (etc) being tracked or tagged specifically, or are you doing text searches on whatever description field (if you have one of those somewhere).

[eluser]Chad Fulton[/eluser]
It seems to me that what you're looking for is a join.

Code:

$query = $this->db->where('jobs.user_id', $user_id)
                  ->join('companies', 'companies.id = jobs.company_id')
                  ->order_by('jobs.end_date')
                  ->get('jobs');

This will give you a list of all of the jobs the user has, ordered by the end date. I would suggest doing the rest of the sorting, etc, in PHP:

Code:

$companies = array();
// Note that since we ordered the jobs by their end date,
// the companies in this array will also be ordered by
// the end date of the job
foreach($query->result() as $row) {
    if(!isset($companies[$row->company_id)) {
        $companies[$row->company_id] = new stdClass();
        $companies[$row->company_id]->company_name = $row->company_name;
        $companies[$row->company_id]->jobs = array();
    }
    $companies[$row->company_id]->jobs[] = array();
}

foreach($companies as $company) {
    echo '<h3>'.$company->company_name.'</h3>';
    echo '<ul>';
    foreach($company->jobs as $job) {
        echo '<li>'.$job->job_title.'</li>';
    }
    echo '</ul>';
}

Obviously this is just a really simple example, but this is the basic idea of what you want, correct?

[eluser]jedd[/eluser]
Oh, is that where you think he's up to?

He said he was fine to do chronological order grabs of data, and I'd assumed that included pulling company details in at the same time.

And that it was now the grouping by company that he was stuck on (or perhaps he meant ordering by company, as a sub-order of chronology).

It's hard to say for sure, but it'll be easier once we see what his current easy query looks like.