Re: Complex(for me) queryset comparisons

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.20 (GNU/Linux)

iEYEARECAAYFAlG5N00ACgkQ12ptzpkpIlsNZACcCqDMioq2JNBkxvxlYFa1lc2j
oiwAniaBP5FU2PKT4AYj9osBAhQAVHX9
=FZ/9
-----END PGP SIGNATURE-----
Hey Matt

Interesting problem!

I think you may need another table though and lose the ForeignKey between
Cable & Bundle. Instead have a linking table like

class CableSet(models.Model)
bundle = models.ForeignKey('Bundle')
cable = models.ForeignKey('Cable')

such that for a specific Bundle you can select which Cables are used to
make it or for a specific Cable select which Bundles it is used in.
Then use this table to construct a new field BundleSet as,
for example, an ordered, comma-separated list of Cable ids which together
make a Bundle. This new field can be added to the Bundles table as

bundleset = models.CommaSeparatedIntegerField(null=True, blank=True)

This computed value allows you to determine which Bundles are the same (in
terms of the cables used) with a simple equality test on this field and so
perform the analyses you want.

This should all be possible within Django though it would need some glue
to hold it all together.

I would think you might gain even more benefits by not considering a
cable to include the two connectors initially; that is consider the bundle
to be composed of different types of cable to which connectors are added
afterwards - not in the real-world sense but as an abstraction of what a
bundle/cable is. Not being that familiar with Django myself, I would
suspect an elaborate model like this might be better designed in SQL using
custom types and stored procedures. After that was working, it should be
real easy to map Django models to the data.

All off the top of my head so hope it makes sense to you and that it might
actually work :)

On Wed, 12 Jun 2013 18:16:55 -0700 (PDT)
MattDale <mattdalesmail@gmail.com> wrote:

> I am having an issue coming up with a way to analyze data that I've been
> collecting. I would rather not change my model definition at this point,
> but I am open to suggestions.
>
> A Bundle is a group of audio/video/AC cables that are taped together in
> our warehouse to our client's specification. We build hundreds of these
> per month and would like to analyze the most common Bundles that we
> create to increase efficiency.
>
> Here is my model definition:
>
> class Bundle(models.Model):
> name = models.CharField(max_length=50)
> length = models.CharField(max_length=50, choices=LENGTH)
> created = models.DateField(auto_now=True)
> archived = models.BooleanField(default=False)
>
> class Cable(models.Model):
> cable_type = models.CharField(max_length=50, choices=CABLE_TYPES)
> origin_cable_connector = models.CharField(
> max_length=50, choices=CONNECTORS, verbose_name='Taped End')
> destination_cable_connector = models.CharField(
> max_length=50, choices=CONNECTORS, verbose_name='Non-taped
> End') bundle = models.ForeignKey('Bundle')
>
> Some examples for the constants:
> CABLE_TYPES: powercon, 19pr, XLR
> CONNECTORS: powercon(blue), powercon(grey), G3(male), G3(female),
> XLR(Male), XLR(Female),
> LENGTH: 25ft, 150ft, 200ft
>
> If I wanted to know the number of times a similar Bundle is created, I
> wanted to use something like:
>
> repeated_sets = {}
> all_cable_sets = [list(sorted(b.cable_set.all())) for b in
> Bundle.objects.all()]
>
> for set in all_cable_sets:
> occurences = all_cable_sets.count(set)
> print occurences
> if occurences > 1:
> if set not in repeated_sets:
> repeated_sets[set] = occurences
>
> This doesn't work because each set belongs to a different bundle, so
> they are all unique and occurences never goes greater than 1.
>
> I would also like to know the lengths of these similar Bundles and the
> number of times identical bundles were created where the cable_sets are
> all the same and their parent Bundle's length is the same. I figured I
> should attempt the simpler query first without the length, and add
> length later by iterating through the repeated_sets dictionary and
> querying for each cable_set's parent Bundle and generating a new
> dictionary out of that.
>
> Should I be approaching this from a different angle? Is there a nice
> Django way of making these comparisons and counts?
>
> Thank you,
>
> -Matt
>



--
Drew Ferguson