Hi everyone, I am running into a really tough time trying to work on a task. I have a Company model with fields such as name, website, zip, email, phone_no. And if any 2 of these are already in the database together as a company instance, I will basically trigger a “company match”, and send an email to the company owner. I have tried many ways, including filtering with Q objects. Here is my latest code.
company_obj, c_created = Company.objects.filter(
(Q(name=company) |
Q(primary_email=email) |
Q(website=website) |
Q(phone_no=phone_no) |
Q(zip=zip))
).get_or_create(name=company.title())
if c_created:
company_obj.name = company.title()
company_obj.website = website
company_obj.street = street
company_obj.city = city
company_obj.state = state
company_obj.phone_no = phone_no
company_obj.primary_email = email
company_obj.zip = zip
company_obj.employee.add(user)
company_obj.save()
else:
'''email company owner'''
This still will not create a different company instance even if just the company name is the same. But I’m looking for a way to match any 2+ criteria. Not just company name, because I will allow a user to duplicate a company name, as long as all the other fields do not match.
Any help in the right direction is greatly appreciated.
EDIT:
BTW, This is coming from a POST request.
if request.method == 'POST':
first_name = request.POST['first_name']
last_name = request.POST['last_name']
company = request.POST['company']
account = request.POST['account']
business = request.POST.getlist('business', '')
website = request.POST['website']
phone_no = request.POST['phone_no']
street = request.POST['street']
city = request.POST['city']
state = request.POST['state']
zip_code = request.POST['zip_code']
email = request.POST['email']
password = request.POST['password']
I’m rewording this to try and verify my understanding of the situation -
A form is being submitted with some fields.
If any two fields match an existing entry, do “something” (in this case, send an email)
For clarification, does “something” need to happen if any two fields match? Or does “something” happen if company name plus any other field matches?
Yes you got it. “something” needs to happen if ANY two fields match. Not necessarily company name. for example, zip+phone=match.
I think this makes it a little more tricky, rather than having name + any other match.
This here achieves name + any other match, so I need to tweak a few things…
company_obj, c_created = Company.objects.filter(
Q(name=company) &
(Q(phone_no=phone_no) |
Q(zip=str(zip_code)) |
Q(primary_email=email) |
Q(website=website))
).get_or_create(name=company)
What I’ve come up with is a way to annotate each row with a numeric value of each comparison. It adds the annotations together to determine how many fields match. (The sum will be 0 if no fields match, 1 if 1 field matches, 2 if 2 fields match, and so on.)
So consider something like this:
Company.objects.annotate(
match_count=Case(When(name=name, then=1), default=0, output_field=IntegerField())
).annotate(match_count=F('match_count')+Case(When(phone_no=phone_no, then=1), default=0, output_field=IntegerField())
).annotate(match_count=F('match_count')+Case(When(street=street, then=1), default=0, output_field=IntegerField())
<repeat the above in style for the rest of the fields>
).filter(match_count__gt=1)
This will return all rows matching more than one field.
(It is possible that someone could fill out the form with two of the fields matching one company, another two fields matching a different company, and yet another two fields matching a third, and so on, for as many fields are being supplied.)
If you just want to determine whether or not such a conflict exists, add the exists() method after the filter to return a simple True or False result.
Ken
1 Like
Wow, thanks Ken! I have never used Case, not annotations yet when Querying.
Right now, I’ve got
company_obj = Company.objects.annotate(
match_count=Case(When(name=comp, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(phone_no=phone_no, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(zip=str(zip_code), then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(primary_email=email, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(website=website, then=1), default=0, output_field=IntegerField())
).filter(match_count__gt=1).exists()
if not company_obj:
Company.objects.create(
name=comp,
zip=zip_code,
primary_email=email,
website=website,
phone_no=phone_no
)
else:
'''Send email to owner'''
All looks good syntactically, I’ve imported F, When, Case, IntegerFields, But getting a NameError: name 'Case' is not defined.
I was doing some research, and was wondering if using Count was mandatory when using F objects?
Name error generally means there’s something that wasn’t imported.
In this case (no pun intended), Case gets imported from django.db.models. However, it may also be a situation of mis-matched parens here.
(And no, “Count” isn’t mandatory.)
Please post the complete view if you think you’ve already got it in there. There might be something else wrong.
(Also, when posting code, please enclose it between lines consisting only of three backticks - `. So you would have one line of only ```, then your code, then another line of ```. )
Finally, if you are getting an error message, please include the full stackdump. Sometimes there’s useful information there that isn’t otherwise obvious.
Sorry, error on my end. I was debugging in the console, and forgot some imports. My mistake. Also learning how to use the Django logging as well. I’m a JR at a startup. A lot to learn!
All look like its working really well. I’m hitting my else statement if a company match is made! Here is my new current code
company_obj = Company.objects.annotate(
match_count=Case(When(name=comp, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(phone_no=phone_no, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(zip=str(zip_code), then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(primary_email=email, then=1), default=0, output_field=IntegerField())).annotate(
match_count=F('match_count') + Case(When(website=website, then=1), default=0, output_field=IntegerField())
).filter(match_count__gt=1)
logger.debug(company_obj)
if not company_obj:
instance = Company.objects.create(
name=comp,
zip=zip_code,
primary_email=email,
website=website,
phone_no=phone_no,
street=street,
city=city,
state=state,
)
instance.owner.add(user)
else:
'''Add employee to company, and send email to Cue admin for approval'''
logger.debug('Hit the else statement')
company_obj.employee.add(user)
Now my next task is to add the employee to the company if the else statement is hit, which it is hitting.(I threw the logger.debug("hit the else statement") in there to make sure it was ouputting in my console). I have a field employee in the Company model. I’m guessing I cant actually grab that company_obj, directly since it is actually a QuerySet?
You can - but what you need to remember is that you may have more than one. (For example, looking at the snippet you posted, a person could enter a name and phone number from one company and the email and website from a completely different company. That means that the filter will return two entries.
You can iterate over company_obj (which, since it is a QuerySet would more accurately be named company_list), performing some action with each entry.
Also, something else I’ll mention as a general interest item - at no point in time should a zip code ever be handled as a numeric entry. If you have anything that causes a zip to be interpreted as an integer, you will have problems. (Think about all the zip codes that start with 0, and what happens if it’s ever converted to an integer and then reconverted back to a string…)
Ken
1 Like
Ah I did not think about that!
Thank you so much for the help Ken, definitely learned a lot today! Greatly, greatly appreciated!