Loading ...
Sorry, an error occurred while loading the content.

Re: Inner Join Extension

Expand Messages
  • taffydb-owner@yahoogroups.com
    Alright, so I think this is final . Changed it back to we return a query instead of a new DB and I m removing the extra right___id and right___s. The
    Message 1 of 34 , Jul 10, 2012
    • 0 Attachment
      Alright, so I think this is "final". Changed it back to we return a query instead of a new DB and I'm removing the extra right___id and right___s. The remaining ___id points to the record id for the DB we just created.

      I'm happy to add this. I think I will mark it as experimental in the docs just in case we come up with changes but I plan on using it in the app I'm currently working on:

      https://gist.github.com/3078697

      Go for inclusion?

      Also, any thoughts on making it easier to build extensions? If nothing else I'm going to add settings to this.context() so they can be accessed from within an extension.

      --- In taffydb@yahoogroups.com, Ian Toltz <itoltz@...> wrote:
      >
      > I believe if you do two successive joins, where all tables have a column of
      > the same name, the third table's value is going to get named right_foo as
      > well and overwrite the second table's datum.
      >
      > On Mon, Jul 9, 2012 at 7:27 PM, Michael Mikowski <z_mikowski@...>wrote:
      >
      > > **
      > >
      > >
      > > Oh, and this should be a better fix for the right-table-prefix problem
      > > using hasOwnProperty().
      > >
      > > I left out your recent updates simply because it was easier for me to
      > > adjust the earlier code.
      > >
      > > 99 for (i in right_row) {
      > > 100 if ( right_row.hasOwnProperty(i) ) {
      > > 101 prefix = out_map.hasOwnProperty(i) ? 'right_' : '';
      > > 102 out_map[prefix + String(i) ] = right_row[i];
      > > 103 }
      > > 104 }
      > >
      > >
      > > ------------------------------
      > > *From:* Michael Mikowski <z_mikowski@...>
      > > *To:* "taffydb@yahoogroups.com" <taffydb@yahoogroups.com>
      > > *Sent:* Monday, July 9, 2012 3:47 PM
      > >
      > > *Subject:* Re: [taffydb] Re: Inner Join Extension
      > >
      > >
      > > I agree with Ian here:
      > > DB().innerJoin().each();
      > > sure looks better than
      > > DB().innerJoin()().each();
      > >
      > > Also, I question the retention of all "private" vars in the results.
      > > After a few generations of joins, won't we end up with something like this:
      > >
      > > { __id : ...,
      > > __original_id : ...,
      > > __right__id : ...,
      > > right__original__id : ...,
      > > right__right__original__id : ...,
      > > right__right__right__original__id : ...,
      > > ...
      > > }
      > >
      > > I would strongly recommend the return not try to be that magical. Yes,
      > > this is potentially useful information in some cases, but I think its
      > > overhead and memory usage far outweighs any advantage.
      > >
      > > If, as some point, we implement an "AS" or "mapping" clause, then things
      > > get much easier. The following is an off-the-cuff example of a mapping
      > > clause in the options_map:
      > >
      > > options_map = {
      > > output_name_map : {
      > > left_map : {
      > > { id : 'user_id' },
      > > { __id : original__id' }
      > > },
      > > right_map : {
      > > { id : undefined }, // remove this from results
      > > { __id : 'right__id' },
      > > }
      > > // ... more options here
      > > };
      > >
      > > Good catch the prefix calculation, btw.
      > >
      > > Cheer, Mike
      > >
      > >
      > > ------------------------------
      > > *From:* Ian Toltz <itoltz@...>
      > > *To:* taffydb@yahoogroups.com
      > > *Sent:* Monday, July 9, 2012 1:58 PM
      > > *Subject:* Re: [taffydb] Re: Inner Join Extension
      > >
      > >
      > > I think it makes more sense to return the result object. It's not about
      > > making a new database that you can manipulate, it's about getting a table
      > > that you can work with like any other table. In particular, returning
      > > TAFFY(foo) instead of TAFFY(foo)() means that you've got to add an extra
      > > set of parens in order to chain new commands onto it, which is something
      > > that both feels natural from a SQL stand point and, thanks to e.g. JQuery,
      > > from a JavaScript standpoint. COmpare:
      > >
      > > DB().innerJoin().each()...
      > >
      > > to
      > >
      > > DB().innerJoin()().each()...
      > >
      > > -Ian
      > >
      > > On Mon, Jul 9, 2012 at 4:55 PM, <taffydb-owner@yahoogroups.com> wrote:
      > >
      > > **
      > >
      > > Ok, so I think I have my head wrapped around this better. In some ways
      > > this is
      > > less about joining tables together for querying and more about building new
      > > tables off the results of a join.
      > >
      > > Here are a few minor proposed changes:
      > >
      > > https://gist.github.com/3078697
      > >
      > > 1. I dump the extra ___s value. It will be overwritten and added back via
      > > the
      > > TAFFY() call.
      > > 2. I keep the ___id values as ___original___id and ___right___id. This is
      > > actually useful information if you work primarily with the joined db() but
      > > what
      > > to make an update to one of the tables you joined to.
      > > 3. I return the new TAFFY(result_list) instead of TAFFY(result_list)(). The
      > > reason being that you've done the work to create the DB which means
      > > .update()
      > > and .remove() could be useful, but you don't have a pointer to the DB if
      > > you
      > > return TAFFY(result_list)().
      > > 4. I renamed it in the extension to .join(). If there is a strong case for
      > > innerJoin() I'd be happy to hear it. But in TaffyDB 2.0 I tried to
      > > simplify the
      > > method names (.orderBy() becoming .order() for example).
      > > 5. I fixed a possible bug in fnCombineRow where falsely values could be
      > > overwritten.
      > >
      > > Given these changes the optimal use case would look like:
      > >
      > > var cityAndState = city_db()
      > > .join( state_db, [ 'state', 'abbreviation' ]);
      > >
      > > cityAndState().each( function (r) { console.log(r); } );
      > >
      > > Thoughts? Or should I push it?
      > >
      > >
      > >
      > >
      > > --
      > > -Ian Toltz
      > >
      > >
      > >
      > >
      > >
      > >
      >
      >
      >
      > --
      > -Ian Toltz
      >
    • Mike
      Agreed. Excellent points.
      Message 34 of 34 , Jul 10, 2012
      • 0 Attachment
        Agreed. Excellent points.

        Ian Toltz <itoltz@...> wrote:

         

        I'd like to see the default operator case actually say "Operator _____ not supported" to make it a bit more explicit, and possibly give a hint to someone who's confused (maybe supplied 3 column names, or something)


        Also, would it make sense to get rid of the columns ___id and ___s from both tables? We end up making a new DB with the merged row, and that DB will create its own ___id and ___s values for each row.

        I could actually see one or both of the original ___ids being useful, but I'm not really sure what the solution is. If nothing else, you can always access them by creating a function to save the old ___ids you care about into a new column, so this is somewhere between low-priority and non-issue.

        -Ian

        On Tue, Jul 10, 2012 at 1:09 PM, <taffydb-owner@yahoogroups.com> wrote:
         

        Alright, so I think this is "final". Changed it back to we return a query instead of a new DB and I'm removing the extra right___id and right___s. The remaining ___id points to the record id for the DB we just created.

        I'm happy to add this. I think I will mark it as experimental in the docs just in case we come up with changes but I plan on using it in the app I'm currently working on:

        https://gist.github.com/3078697

        Go for inclusion?

        Also, any thoughts on making it easier to build extensions? If nothing else I'm going to add settings to this.context() so they can be accessed from within an extension.



        --- In taffydb@yahoogroups.com, Ian Toltz <itoltz@...> wrote:
        >
        > I believe if you do two successive joins, where all tables have a column of
        > the same name, the third table's value is going to get named right_foo as
        > well and overwrite the second table's datum.
        >
        > On Mon, Jul 9, 2012 at 7:27 PM, Michael Mikowski <z_mikowski@...>wrote:
        >
        > > **

        > >
        > >
        > > Oh, and this should be a better fix for the right-table-prefix problem
        > > using hasOwnProperty().
        > >
        > > I left out your recent updates simply because it was easier for me to
        > > adjust the earlier code.
        > >
        > > 99 for (i in right_row) {
        > > 100 if ( right_row.hasOwnProperty(i) ) {
        > > 101 prefix = out_map.hasOwnProperty(i) ? 'right_' : '';
        > > 102 out_map[prefix + String(i) ] = right_row[i];
        > > 103 }
        > > 104 }
        > >
        > >
        > > ------------------------------
        > > *From:* Michael Mikowski <z_mikowski@...>
        > > *To:* "taffydb@yahoogroups.com" <taffydb@yahoogroups.com>
        > > *Sent:* Monday, July 9, 2012 3:47 PM
        > >
        > > *Subject:* Re: [taffydb] Re: Inner Join Extension

        > >
        > >
        > > I agree with Ian here:
        > > DB().innerJoin().each();
        > > sure looks better than
        > > DB().innerJoin()().each();
        > >
        > > Also, I question the retention of all "private" vars in the results.
        > > After a few generations of joins, won't we end up with something like this:
        > >
        > > { __id : ...,
        > > __original_id : ...,
        > > __right__id : ...,
        > > right__original__id : ...,
        > > right__right__original__id : ...,
        > > right__right__right__original__id : ...,
        > > ...
        > > }
        > >
        > > I would strongly recommend the return not try to be that magical. Yes,
        > > this is potentially useful information in some cases, but I think its
        > > overhead and memory usage far outweighs any advantage.
        > >
        > > If, as some point, we implement an "AS" or "mapping" clause, then things
        > > get much easier. The following is an off-the-cuff example of a mapping
        > > clause in the options_map:
        > >
        > > options_map = {
        > > output_name_map : {
        > > left_map : {
        > > { id : 'user_id' },
        > > { __id : original__id' }
        > > },
        > > right_map : {
        > > { id : undefined }, // remove this from results
        > > { __id : 'right__id' },
        > > }
        > > // ... more options here
        > > };
        > >
        > > Good catch the prefix calculation, btw.
        > >
        > > Cheer, Mike
        > >
        > >
        > > ------------------------------
        > > *From:* Ian Toltz <itoltz@...>
        > > *To:* taffydb@yahoogroups.com
        > > *Sent:* Monday, July 9, 2012 1:58 PM
        > > *Subject:* Re: [taffydb] Re: Inner Join Extension

        > >
        > >
        > > I think it makes more sense to return the result object. It's not about
        > > making a new database that you can manipulate, it's about getting a table
        > > that you can work with like any other table. In particular, returning
        > > TAFFY(foo) instead of TAFFY(foo)() means that you've got to add an extra
        > > set of parens in order to chain new commands onto it, which is something
        > > that both feels natural from a SQL stand point and, thanks to e.g. JQuery,
        > > from a JavaScript standpoint. COmpare:
        > >
        > > DB().innerJoin().each()...
        > >
        > > to
        > >
        > > DB().innerJoin()().each()...
        > >
        > > -Ian
        > >
        > > On Mon, Jul 9, 2012 at 4:55 PM, <taffydb-owner@yahoogroups.com> wrote:
        > >
        > > **

        > >
        > > Ok, so I think I have my head wrapped around this better. In some ways
        > > this is
        > > less about joining tables together for querying and more about building new
        > > tables off the results of a join.
        > >
        > > Here are a few minor proposed changes:
        > >
        > > https://gist.github.com/3078697
        > >
        > > 1. I dump the extra ___s value. It will be overwritten and added back via
        > > the
        > > TAFFY() call.
        > > 2. I keep the ___id values as ___original___id and ___right___id. This is
        > > actually useful information if you work primarily with the joined db() but
        > > what
        > > to make an update to one of the tables you joined to.
        > > 3. I return the new TAFFY(result_list) instead of TAFFY(result_list)(). The
        > > reason being that you've done the work to create the DB which means
        > > .update()
        > > and .remove() could be useful, but you don't have a pointer to the DB if
        > > you
        > > return TAFFY(result_list)().
        > > 4. I renamed it in the extension to .join(). If there is a strong case for
        > > innerJoin() I'd be happy to hear it. But in TaffyDB 2.0 I tried to
        > > simplify the
        > > method names (.orderBy() becoming .order() for example).
        > > 5. I fixed a possible bug in fnCombineRow where falsely values could be
        > > overwritten.
        > >
        > > Given these changes the optimal use case would look like:
        > >
        > > var cityAndState = city_db()
        > > .join( state_db, [ 'state', 'abbreviation' ]);
        > >
        > > cityAndState().each( function (r) { console.log(r); } );
        > >
        > > Thoughts? Or should I push it?
        > >
        > >
        > >
        > >
        > > --
        > > -Ian Toltz
        > >
        > >
        > >
        > >
        > >
        > >
        >
        >
        >
        > --
        > -Ian Toltz
        >




        --
        -Ian Toltz
      Your message has been successfully submitted and would be delivered to recipients shortly.