If I understand your question, I think you can do this fairly easily.
class File(Entity):
id = Field(Integer, primary_key=True, colname='id_file')
element = ManyToOne('Element') # need to define Element
shots = OneToMany('Shot') # need to define Shot
descriptor = Field(String(256))
...
exports = OneToMany('Export', inverse='exported_file') # use this
property to get all exports of a file
class Export(Entity):
source_file = ManyToOne('File')
exported_file = ManyToOne('File')
export_time = Field(DateTime)
Then, to get the source files for a given file, do the following.
some_file = Files.query.first()
exports = some_file.exports
sources = [export.source_file for export in exports]
Obviously, I've made some assumptions about your model, but I hope
this helps.
Jason
On Jun 2, 2:43 pm, Jason Porath <[EMAIL PROTECTED]> wrote:
> Hey all.
>
> I'm in the process of porting over some SQL code to Elixir, to
> determine how well it'd work as a backend for our pipe. We have some
> fairly complicated queries, and I'm not sure how to model them. Here's
> an example:
>
> SELECT files.id_file, files.id_element, files.descriptor,
> files.id_file_type, files.id_staff AS id_staff_export,
> exports.timestamp,
> source_files.id_staff AS id_staff_files
> FROM files
> LEFT JOIN shots ON shots.id_shot=files.id_shot
> LEFT JOIN exports ON exports.id_file_export=files.id_file
> LEFT JOIN files AS source_files ON
> exports.id_file_source=source_files.id_file
> WHERE shots.id_shot=self.id_shot
> AND files.version='hero'
> ORDER BY files.id_element, descriptor, exports.timestamp DESC
>
> Basically, what is happening is that we have some files being
> exported. In the process, a copy is made. Both original and source are
> tracked in the database, as well as the actual act of exporting
> (stored in "exports" table).
>
> I don't know how to join a table (in this case, "files") twice in
> sqlAlchemy/Elixir. I don't have any leeway in changing the structure
> of the data recording, unfortunately.
>
> Could anyone shed some light on this?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"SQLElixir" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlelixir?hl=en
-~----------~----~----~----~------~----~------~--~---
