1

I am working with a pre-existing database called Employee. I have three separate fields i'd like to combine into a single field, but I can't add an additional field to the pre-exisiting database.

I know the proper way to combine multiple fields into one field using python is 

'%s - %s %s' % (self.username, self.firstname, self.lastname)

However, I can't call self outside the model, or at least i'm not sure where I would call self.

My end goal is to have a select box with the combined field a user can search either first, last, or account name. My current model looks like the following:

class Employee(models.Model):
        staff_id = models.IntegerField(db_column = 'Employee_ID')
        status_id = models.IntegerField(db_column = 'StatusID')
        username = models.CharField(db_column = 'SamAccountName',primary_key = True, max_length = 31)
        lastname = models.CharField(db_column = 'Surname', max_length = 63)
        firstname = models.CharField(db_column = 'GivenName', max_length = 63)
        title = models.CharField(db_column = 'Title', max_length = 127)

        class Meta:
            managed = False
            db_table = '[Employee]'

I tried to add to my model, but when I call full_username it says the field doesn't exists, which is true because there isn't a field in the database. We aren't allowed to add a new field to the database. 

def get_full_name(self):

        full_username = '%s - %s %s' % (self.username, self.firstname, self.lastname)

        return full_username.split()

Ideally i'd want my view to look something like this (i know it wont' work as is, i'd replace that with 'full_username):

activeuserlist = Employee.objects.filter(staff_id = '1').values_list('%s - %s %s' % (Employee.username, Employee.firstname, Employee.lastname), flat = True)

How would I get the full name added to my view, what am I missing with my logic or where would be the correct place to put it?

user1470034
  • 671
  • 2
  • 8
  • 23

4 Answers4

2

You can give this a try:

from django.db.models.functions import Concat
from django.db.models import F, Value

employees = Employee.objects.annotate(full_username=Concat(F('username'), Value(' - '), F('firstname'), Value(' '), F('lastname')))\
    .filter(staff_id='1', full_username__icontains='hello')

The icontains bits is just a demo, with this query you can filter the result based on the combined name as well.

If you have to use this everywhere, then I recommend you create your own queryset/manager in your model then put this annotation into the default queryset. After that you can use your full_username filter any where you want without having to add the annotation first.

Du D.
  • 5,062
  • 2
  • 29
  • 34
  • When I try to use concat, it gives me the following AttributeError: can't set attribute [25/Jan/2018 12:06:25] "GET /account/profile/ HTTP/1.1" 500 96620 probably because i'm not trying to filter down to a single username, but a list. – user1470034 Jan 25 '18 at 18:07
  • You might to reference the actual column name instead of the alias, ie. F('username') would be F('SamAccountName') – Du D. Jan 25 '18 at 18:13
  • I tried that and it still throws AttributeError at /account/profile/ can't set attribute – user1470034 Jan 25 '18 at 18:18
  • can i see ur query? – Du D. Jan 25 '18 at 18:19
  • activeuserlist = Employee.objects.annotate(full_username=Concat(F('samaccountname'), Value(' - '), F('givenname'), Value(' '), F('surname')))\ .filter(staff_id ='1') When I tried the original it threw an error to give me the list of field names that existed, after i corrected I get the can not set attribute error. – user1470034 Jan 25 '18 at 18:23
  • I found after some research that having a @property decorator on the model which was suggested below was causing it to throw an error. I marked your solution correct. Thank you for your help. – user1470034 Jan 25 '18 at 19:51
0

First, try formatting the string in a good way

full_username = '{} - {} {}'.format(self.username, self.firstname, self.lastname)

Second you can the method get_full_name() in model and call it from the model object.

employee = Employee.objects.get(id=1) full_name = employee.get_full_name()

That should work. :)

Green Eagle
  • 227
  • 3
  • 12
  • That only works if there was one employee, but i'm looking for all active employees. – user1470034 Jan 25 '18 at 17:58
  • It is the same if you loop the list you will get single object and you can access in the same way... – Green Eagle Jan 25 '18 at 18:04
  • I get 'QuerySet' object has no attribute 'get_full_username' using the following. emplist= Employee.objects.all() activeempoloyee = emplist.get_full_username my model shows the following: @property def full_username(self): full_username = '{} - {} {}'.format(self.username, self.firstname, self.lastname) return (full_username) – user1470034 Jan 25 '18 at 19:36
0

Try using a property:

from django.db import models

class Person(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    birth_date = models.DateField()

    def baby_boomer_status(self):
        "Returns the person's baby-boomer status."
        import datetime
        if self.birth_date < datetime.date(1945, 8, 1):
            return "Pre-boomer"
        elif self.birth_date < datetime.date(1965, 1, 1):
            return "Baby boomer"
        else:
            return "Post-boomer"

    @property
    def full_name(self):
        "Returns the person's full name."
        return '%s %s' % (self.first_name, self.last_name)
fraczles
  • 81
  • 3
0

If you don't need the functionality of a QuerySet, try this

activeuserlist = [
    '{} - {} {}'.format(user.username, user.firstname, user.lastname)
    for user in Employee.objects.filter(staff_id = '1')
]

If you do need a QuerySet, I think it's not possible on python-level, only on SQL-level. See this thread on annotations.

ascripter
  • 5,665
  • 12
  • 45
  • 68