class CountField(AggregateField):
    """
    Count aggregation
    """
    function_name = 'Count' class AvgField(AggregateField):
    """
    Average aggregation
    """
    function_name = 'Avg' class MaxField(AggregateField):
    """
    Maximum aggregation
    """
    function_name = 'Max' class MinField(AggregateField):
    """
    Minimum aggregation
    """
    function_name = 'Min' class StdDevField(AggregateField):
    """
    Standard deviation aggregation
    """
    function_name = 'StdDev' class SumField(AggregateField):
    """
    Summation aggregation
    """
    function_name = 'Sum' class VarianceField(AggregateField):
    """
    Variance window function
    """
    function_name = 'Variance' class Query(object):
    """
    A Query instance represents an actual query that will be executed. It provides
    methods for selecting fields from tables, inner queries, joins, filtering,
    limiting, and sorting. Different types of queries can be executed, such as:
    select, update, delete, create, and explain.

    Properties:

        sql: str
            The query generated by calling ``self.get_sql()`` This is used for
            caching purposes.

        tables: list of Table
            A list of ``Table`` instances this query is selecting from

        joins: list of Join
            A list of ``Join`` instances this query is joining on

        _where: Where
            A ``Where`` instance containing filtering data for this query

        groups: list of Group
            A list of ``Group`` instances that determine the GROUP BY clause for this query

        sorters: list of Sorter
            A list of ``Sorter`` instances that determine the ORDER BY clause for this query

        _limit: Limit
            An instance of ``Limit`` This will only exist if a limit has been specified for the query

        table_prefix: str
            A ``str`` that determines how to prefix inner queries of this query

    """
    enable_safe_limit = False
    safe_limit = 1000

    def init_defaults(self):
        """
        Sets the default values for this instance
        """
        self.sql = ''
        self.tables = []
        self.joins = []
        self._where = Where()
        self.groups = []
        self.sorters = []
        self._limit = None
        self.table_prefix = ''
        self.is_inner = False
        self.with_tables = []
        self._distinct = False
        self.distinct_ons = []
        self.field_names = []
        self.field_names_pk = None
        self.values = []

    def __init__(self, connection=None):
        """
        Initializes this instance by calling ``self.init_defaults``

        :type connection: :class:`DatabaseWrapper <django:django.db.backends.BaseDatabaseWrapper>`
        :parameter connection: A Django database connection. This can be used to connect to
            databases other than your default database.

        .. code-block:: python

            from django.db import connections
            from querybuilder.query import Query

            Query(connections.all()[0]).from_table('auth_user').count()
            # 15L
            Query(connections.all()[1]).from_table('auth_user').count()
            # 223L

        """
        self.connection = connection or default_django_connection

        self.init_defaults()

    def get_cursor(self):
        """
        Get a cursor for the Query's connection

        :rtype: :class:`CursorDebugWrapper <django:django.db.backends.util.CursorDebugWrapper>`
        :returns: A database cursor
        """
        return self.connection.cursor()

    def from_table(self, table=None, fields='*', schema=None, **kwargs):
        """
        Adds a ``Table`` and any optional fields to the list of tables
        this query is selecting from.

        :type table: str or dict or :class:`Table <querybuilder.tables.Table>`
            or :class:`Query <querybuilder.query.Query>` or
            :class:`ModelBase <django:django.db.models.base.ModelBase>`
        :param table: The table to select fields from. This can be a string of the table
            name, a dict of {'alias': table}, a ``Table`` instance, a Query instance, or a
            django Model instance

        :type fields: str or tuple or list or Field
        :param fields: The fields to select from ``table``. Defaults to '*'. This can be
            a single field, a tuple of fields, or a list of fields. Each field can be a string
            or ``Field`` instance

        :type schema: str
        :param schema: This is not implemented, but it will be a string of the db schema name

        :param kwargs: Any additional parameters to be passed into the constructor of ``TableFactory``

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        # self.mark_dirty()

        self.tables.append(TableFactory(
            table=table,
            fields=fields,
            schema=schema,
            owner=self,
            **kwargs
        ))

        return self

    def insert_into(self, table=None, field_names=None, values=None, **kwargs):
        """
        Bulk inserts a list of values into a table

        :type table: str or dict or :class:`Table <querybuilder.tables.Table>`
            or :class:`Query <querybuilder.query.Query>` or :class:`ModelBase <django:django.db.models.base.ModelBase>`
        :param table: The table to select fields from. This can be a string of the table
            name, a dict of {'alias': table}, a ``Table`` instance, a Query instance, or a
            django Model instance

        :type field_names: list
        :param field_names: A list of ordered field names that relate to the data in the values list

        :type values: list of list
        :param values: A list each values list with the values in the same order as the field names

        :param kwargs: Any additional parameters to be passed into the constructor of ``TableFactory``

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        table = TableFactory(
            table=table,
            **kwargs
        )
        self.tables.append(table)

        self.field_names = field_names
        self.values = values

        return self

    def update_table(self, table=None, field_names=None, values=None, pk=None, **kwargs):
        """
        Bulk updates rows in a table

        :type table: str or dict or :class:`Table <querybuilder.tables.Table>`
            or :class:`Query <querybuilder.query.Query>` or :class:`ModelBase <django:django.db.models.base.ModelBase>`
        :param table: The table to select fields from. This can be a string of the table
            name, a dict of {'alias': table}, a ``Table`` instance, a Query instance, or a
            django Model instance

        :type field_names: list
        :param field_names: A list of ordered field names that relate to the data in the values list

        :type values: list of list
        :param values: A list each values list with the values in the same order as the field names

        :type pk: int
        :param pk: The name of the primary key in the table and field_names

        :param kwargs: Any additional parameters to be passed into the constructor of ``TableFactory``

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: self
        """
        table = TableFactory(
            table=table,
            **kwargs
        )
        self.tables.append(table)

        self.field_names = field_names
        self.values = values
        self.field_names_pk = pk

    # TODO: add docs
    # TODO: add tests for custom with clauses
    def with_query(self, query=None, alias=None):
        """

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        self.with_tables.append(TableFactory(query, alias=alias))
        return self

    def join(self, right_table=None, fields=None, condition=None, join_type='JOIN',
             schema=None, left_table=None, extract_fields=True, prefix_fields=False, field_prefix=None,
             allow_duplicates=False):
        """
        Joins a table to another table based on a condition and adds fields from the joined table
        to the returned fields.

        :type right_table: str or dict or :class:`Table <querybuilder.tables.Table>`
        :param right_table: The table being joined with. This can be a string of the table
            name, a dict of {'alias': table}, or a ``Table`` instance

        :type fields: str or tuple or list or :class:`Field <querybuilder.fields.Field>`
        :param fields: The fields to select from ``right_table``. Defaults to `None`. This can be
            a single field, a tuple of fields, or a list of fields. Each field can be a string
            or ``Field`` instance

        :type condition: str
        :param condition: The join condition specifying the fields being joined. If the two tables being
            joined are instances of ``ModelTable`` then the condition should be created automatically.

        :type join_type: str
        :param join_type: The type of join (JOIN, LEFT JOIN, INNER JOIN, etc). Defaults to 'JOIN'

        :type schema: str
        :param schema: This is not implemented, but it will be a string of the db schema name

        :type left_table: str or dict or Table
        :param left_table: The left table being joined with. This can be a string of the table
            name, a dict of {'alias': table}, or a ``Table`` instance. Defaults to the first table
            in the query.

        :type extract_fields: bool
        :param extract_fields: If True and joining with a ``ModelTable``, then '*'
            fields will be converted to individual fields for each column in the table. Defaults
            to True.

        :type prefix_fields: bool
        :param prefix_fields: If True, then the joined table will have each of its field names
            prefixed with the field_prefix. If not field_prefix is specified, a name will be
            generated based on the join field name. This is usually used with nesting results
            in order to create models in python or javascript. Defaults to True.

        :type field_prefix: str
        :param field_prefix: The field prefix to be used in front of each field name if prefix_fields
            is set to True. If no field_prefix is set, one will be automatically created based on
            the join field name.

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: self
        """
        # self.mark_dirty()
        # TODO: fix bug when joining from simple table to model table with no condition
        # it assumes left_table.model

        # if there is no left table, assume the query's first table
        # TODO: add test for auto left table to replace old auto left table
        # if left_table is None and len(self.tables):
        #     left_table = self.tables[0]

        # left_table = TableFactory(left_table)
        # right_table = TableFactory(right_table)

        # create the join item
        new_join_item = Join(
            left_table=left_table,
            right_table=right_table,
            fields=fields,
            condition=condition,
            join_type=join_type,
            schema=schema,
            owner=self,
            extract_fields=extract_fields,
            prefix_fields=prefix_fields,
            field_prefix=field_prefix,
        )

        # check if this table is already joined upon
        # TODO: add test for this
        if allow_duplicates is False:
            for join_item in self.joins:
                if join_item.right_table.get_identifier() == new_join_item.right_table.get_identifier():
                    if join_item.left_table.get_identifier() == new_join_item.left_table.get_identifier():
                        return self

        self.joins.append(new_join_item)

        return self

    def join_left(self, right_table=None, fields=None, condition=None, join_type='LEFT JOIN',
                  schema=None, left_table=None, extract_fields=True, prefix_fields=False,
                  field_prefix=None, allow_duplicates=False):
        """
        Wrapper for ``self.join`` with a default join of 'LEFT JOIN'

        :type right_table: str or dict or :class:`Table <querybuilder.tables.Table>`
        :param right_table: The table being joined with. This can be a string of the table
            name, a dict of {'alias': table}, or a ``Table`` instance

        :type fields: str or tuple or list or :class:`Field <querybuilder.fields.Field>`
        :param fields: The fields to select from ``right_table``. Defaults to `None`. This can be
            a single field, a tuple of fields, or a list of fields. Each field can be a string
            or ``Field`` instance

        :type condition: str
        :param condition: The join condition specifying the fields being joined. If the two tables being
            joined are instances of ``ModelTable`` then the condition should be created automatically.

        :type join_type: str
        :param join_type: The type of join (JOIN, LEFT JOIN, INNER JOIN, etc). Defaults to 'JOIN'

        :type schema: str
        :param schema: This is not implemented, but it will be a string of the db schema name

        :type left_table: str or dict or :class:`Table <querybuilder.tables.Table>`
        :param left_table: The left table being joined with. This can be a string of the table
            name, a dict of {'alias': table}, or a ``Table`` instance. Defaults to the first table
            in the query.

        :type extract_fields: bool
        :param extract_fields: If True and joining with a ``ModelTable``, then '*'
            fields will be converted to individual fields for each column in the table. Defaults
            to True.

        :type prefix_fields: bool
        :param prefix_fields: If True, then the joined table will have each of its field names
            prefixed with the field_prefix. If not field_prefix is specified, a name will be
            generated based on the join field name. This is usually used with nesting results
            in order to create models in python or javascript. Defaults to True.

        :type field_prefix: str
        :param field_prefix: The field prefix to be used in front of each field name if prefix_fields
            is set to True. If no field_prefix is set, one will be automatically created based on
            the join field name.

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        return self.join(
            right_table=right_table,
            fields=fields,
            condition=condition,
            join_type=join_type,
            schema=schema,
            left_table=left_table,
            extract_fields=extract_fields,
            prefix_fields=prefix_fields,
            field_prefix=field_prefix,
            allow_duplicates=allow_duplicates
        )

    def where(self, q=None, where_type='AND', **kwargs):
        """
        Adds a where condition as a Q object to the query's ``Where`` instance.

        :type q: :class:`Q <django:django.db.models.Q>`
        :param q: A django ``Q`` instance. This will be added to the query's ``Where`` object. If no
            Q object is passed, the kwargs will be examined for params to be added to Q objects

        :param where_type: str
        :param where_type: The connection type of the where condition ('AND', 'OR')

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        # self.mark_dirty()
        if q is not None:
            self._where.wheres.add(q, where_type)
        if len(kwargs):
            for key, value in kwargs.items():
                q = Q(**{
                    key: value
                })
                self._where.wheres.add(q, where_type)
        return self

    def group_by(self, field=None, table=None, allow_duplicates=False):
        """
        Adds a group by clause to the query by adding a ``Group`` instance to the query's
        groups list

        :type field: str or dict or :class:`Field <querybuilder.fields.Field>`
        :param field: This can be a string of a field name, a dict of {'alias': field}, or
            a ``Field`` instance

        :type table: str or dict or :class:`Table <querybuilder.table.Table>`
        :param table: Optional. This can be a string of a table name, a dict of {'alias': table}, or
            a ``Table`` instance. A table only needs to be supplied in more complex queries where
            the field name is ambiguous.

        :return: self
        :rtype: :class:`Query <querybuilder.query.Query>`
        """
        new_group_item = Group(
            field=field,
            table=table,
        )

        if allow_duplicates is False:
            for group_item in self.groups:
                if group_item.field.get_identifier() == new_group_item.field.get_identifier():
                    return self

        self.groups.append(new_group_item)

        return self

    def order_by(self, field=None, table=None, desc=False):
        """
        Adds an order by clause to the query by adding a ``Sorter`` instance to the query's
        sorters list

        :type field: str or dict or :class:`Field <querybuilder.fields.Field>`
        :param field: This can be a string of a field name, a dict of {'alias': field}, or
            a ``Field`` instance

        :type table: str or dict or :class:`Table <querybuilder.table.Table>`
        :param table: Optional. This can be a string of a table name, a dict of {'alias': table}, or
            a ``Table`` instance. A table only needs to be supplied in more complex queries where
            the field name is ambiguous.

        :type desc: bool
        :param desc: Set to True to sort by this field in DESC order or False to sort by this field
            in ASC order. Defaults to False.

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: self
        """
        self.sorters.append(Sorter(
            field=field,
            table=table,
            desc=desc
        ))
        return self

    def limit(self, limit=None, offset=None):
        """
        Sets a limit and/or offset to the query to limit the number of rows returned.

        :type limit: int
        :param limit: The number of rows to return

        :type offset: int
        :param offset: The offset from the start of the record set where rows should start being returned

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: self
        """
        self._limit = Limit(
            limit=limit,
            offset=offset
        )
        return self

    def distinct(self, use_distinct=True):
        """
        Adds a distinct clause to the query

        :type use_distinct: bool
        :param use_distinct: Whether or not to include the distinct clause

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: self
        """
        self._distinct = use_distinct
        return self

    def distinct_on(self, *fields):
        for field in fields:
            self.distinct_ons.append(FieldFactory(field))
        return self

    def check_name_collisions(self):
        """
        Checks if there are any tables referenced by the same identifier and updated the
        auto_alias accordingly. This is called when generating the sql for a query
        and should only be called internally.
        """
        table_index = 0
        table_names = {}
        for table in self.tables + self.with_tables:
            table_prefix = 'T{0}'.format(table_index)
            auto_alias = '{0}{1}'.format(self.table_prefix, table_prefix)

            identifier = table.get_identifier()
            if identifier is None or identifier in table_names:
                table.auto_alias = auto_alias
            table_names[identifier] = True

            # prefix inner query args and update self args
            if type(table) is QueryTable:
                table.query.prefix_args(auto_alias)
                table.query.table_prefix = auto_alias

            table_index += 1

    def prefix_args(self, prefix):
        """
        Adds an argument prefix to the query's ``Where`` object. This should only
        be called internally.
        """
        self._where.arg_prefix = prefix

    def get_sql(self, debug=False, use_cache=True):
        """
        Generates the sql for this query and returns the sql as a string.

        :type debug: bool
        :param debug: If True, the sql will be returned in a format that is easier to read and debug.
            Defaults to False

        :type use_cache: bool
        :param use_cache: If True, the query will returned the cached sql if it exists rather
            then generating the sql again. If False, the sql will be generated again. Defaults to True.

        :rtype: str
        :return: The generated sql for this query
        """
        # TODO: enable caching
        # if self.sql and use_cache and not debug:
        #     return self.sql

        # auto alias any naming collisions
        self.check_name_collisions()

        # if debugging, return the debug formatted sql
        if debug:
            return self.format_sql()

        # build each part of the query
        sql = ''
        sql += self.build_withs()
        sql += self.build_select_fields()
        sql += self.build_from_table()
        sql += self.build_joins()
        sql += self.build_where()
        sql += self.build_groups()
        sql += self.build_order_by()
        sql += self.build_limit()

        # remove any whitespace from the beginning and end of the sql
        self.sql = sql.strip()

        return self.sql

    def get_insert_sql(self, rows):
        field_names_sql = '({0})'.format(', '.join(self.get_field_names()))
        row_values = []
        sql_args = []
        for row in rows:
            placeholders = []
            for value in row:
                sql_args.append(value)
                placeholders.append('%s')
            row_values.append('({0})'.format(', '.join(placeholders)))
        row_values_sql = ', '.join(row_values)

        self.sql = 'INSERT INTO {0} {1} VALUES {2}'.format(
            self.tables[0].get_identifier(),
            field_names_sql,
            row_values_sql
        )

        return self.sql, sql_args

    def get_update_sql(self, rows):
        """
        Returns SQL UPDATE for rows ``rows``

        .. code-block:: sql

            UPDATE table_name
            SET
                field1 = new_values.field1
                field2 = new_values.field2
            FROM (
                VALUES
                    (1, 'value1', 'value2'),
                    (2, 'value1', 'value2')
            ) AS new_values (id, field1, field2)
            WHERE table_name.id = new_values.id;

        """
        field_names = self.get_field_names()
        pk = field_names[0]
        update_field_names = field_names[1:]

        num_columns = len(rows[0])
        if num_columns < 2:
            raise Exception('At least 2 fields must be passed to get_update_sql')

        all_null_indices = [
            all(row[index] is None for row in rows)
            for index in range(1, num_columns)
        ]

        field_names_sql = '({0})'.format(', '.join(field_names))

        row_values = []
        sql_args = []

        # Loop over each row and put the %s placeholder for each field
        for i, row in enumerate(rows):

            # Build a list of each field's placeholder
            placeholders = []

            # If this is the first row, add casting information so the db knows the field types
            if i == 0 and hasattr(self.tables[0], 'model'):
                for field_index, value in enumerate(row):
                    # Append the value
                    sql_args.append(value)

                    # Figure out how to cast it
                    field_object = self.tables[0].model._meta.get_field(field_names[field_index])
                    db_type = field_object.db_type(self.connection)

                    # Don't cast the pk
                    if db_type in SERIAL_DTYPES:
                        placeholders.append('%s')
                    else:
                        # Cast the placeholder to the data type
                        placeholders.append('%s::{0}'.format(db_type))
            else:
                for value in row:
                    sql_args.append(value)
                    placeholders.append('%s')
            row_values.append('({0})'.format(', '.join(placeholders)))
        row_values_sql = ', '.join(row_values)

        # build field list for SET portion
        set_field_list = [
            '{0} = NULL'.format(field_name)
            if all_null_indices[idx] else '{0} = new_values.{0}'.format(field_name)
            for idx, field_name in enumerate(update_field_names)
        ]
        set_field_list_sql = ', '.join(set_field_list)

        self.sql = 'UPDATE {0} SET {1} FROM (VALUES {2}) AS new_values {3} WHERE {0}.{4} = new_values.{4}'.format(
            self.tables[0].get_identifier(),
            set_field_list_sql,
            row_values_sql,
            field_names_sql,
            pk
        )

        return self.sql, sql_args

    def get_upsert_sql(
        self,
        rows,
        unique_fields,
        update_fields,
        auto_field_name=None,
        only_insert=False,
        return_rows=True
    ):
        """
        Generates the postgres specific sql necessary to perform an upsert (ON CONFLICT)

        INSERT INTO table_name (field1, field2)
        VALUES (1, 'two')
        ON CONFLICT (unique_field) DO UPDATE SET field2 = EXCLUDED.field2;
        """
        ModelClass = self.tables[0].model

        # Use all fields except pk unless the uniqueness constraint is the pk field. Null pk field rows will be
        # excluded in the upsert method before calling this method
        all_fields = [field for field in ModelClass._meta.fields if field.column != auto_field_name]
        if auto_field_name in unique_fields and not only_insert:
            all_fields = [field for field in ModelClass._meta.fields]

        all_field_names = [field.column for field in all_fields]
        all_field_names_sql = ', '.join(all_field_names)

        # Convert field names to db column names
        unique_fields = [
            ModelClass._meta.get_field(unique_field)
            for unique_field in unique_fields
        ]
        update_fields = [
            ModelClass._meta.get_field(update_field)
            for update_field in update_fields
        ]

        unique_field_names_sql = ', '.join([
            field.column for field in unique_fields
        ])
        update_fields_sql = ', '.join([
            '{0} = EXCLUDED.{0}'.format(field.column)
            for field in update_fields
        ])

        row_values = []
        sql_args = []

        for row in rows:
            placeholders = []
            for field in all_fields:
                # Convert field value to db value
                # Use attname here to support fields with custom db_column names
                sql_args.append(field.get_db_prep_save(getattr(row, field.attname), self.connection))
                placeholders.append('%s')
            row_values.append('({0})'.format(', '.join(placeholders)))
        row_values_sql = ', '.join(row_values)

        if update_fields:
            self.sql = 'INSERT INTO {0} ({1}) VALUES {2} ON CONFLICT ({3}) DO UPDATE SET {4} {5}'.format(
                self.tables[0].get_identifier(),
                all_field_names_sql,
                row_values_sql,
                unique_field_names_sql,
                update_fields_sql,
                'RETURNING *' if return_rows else ''
            )
        else:
            self.sql = 'INSERT INTO {0} ({1}) VALUES {2} ON CONFLICT ({3}) {4} {5}'.format(
                self.tables[0].get_identifier(),
                all_field_names_sql,
                row_values_sql,
                unique_field_names_sql,
                'DO UPDATE SET {0}=EXCLUDED.{0}'.format(unique_fields[0].column),
                'RETURNING *' if return_rows else ''
            )

        return self.sql, sql_args

    def format_sql(self):
        """
        Builds the sql in a format that is easy for humans to read and debug

        :return: The formatted sql for this query
        :rtype: str
        """
        # TODO: finish adding the other parts of the sql generation
        sql = ''

        # build SELECT
        select_segment = self.build_select_fields()
        select_segment = select_segment.replace('SELECT ', '', 1)
        fields = [field.strip() for field in select_segment.split(',')]
        sql += 'SELECT\n\t{0}\n'.format(',\n\t'.join(fields))

        # build FROM
        from_segment = self.build_from_table()
        from_segment = from_segment.replace('FROM ', '', 1)
        tables = [table.strip() for table in from_segment.split(',')]
        sql += 'FROM\n\t{0}\n'.format(',\n\t'.join(tables))

        # build ORDER BY
        order_by_segment = self.build_order_by()
        if len(order_by_segment):
            order_by_segment = order_by_segment.replace('ORDER BY ', '', 1)
            sorters = [sorter.strip() for sorter in order_by_segment.split(',')]
            sql += 'ORDER BY\n\t{0}\n'.format(',\n\t'.join(sorters))

        # build LIMIT
        limit_segment = self.build_limit()
        if len(limit_segment):
            if 'LIMIT' in limit_segment:
                limit_segment = limit_segment.replace('LIMIT ', 'LIMIT\n\t', 1)
                if 'OFFSET' in limit_segment:
                    limit_segment = limit_segment.replace('OFFSET ', '\nOFFSET\n\t', 1)
            elif 'OFFSET' in limit_segment:
                limit_segment = limit_segment.replace('OFFSET ', 'OFFSET\n\t', 1)
            sql += limit_segment

        return sql

    def get_field_names(self):
        """
        Builds a list of the field names for all tables and joined tables by calling
        ``get_field_names()`` on each table

        :return: list of field names
        :rtype: list of str
        """
        field_names = []
        for table in self.tables:
            field_names.extend(table.get_field_names())
        for join_item in self.joins:
            field_names.extend(join_item.right_table.get_field_names())
        return field_names

    def get_field_identifiers(self):
        """
        Builds a list of the field identifiers for all tables and joined tables by calling
        ``get_field_identifiers()`` on each table

        :return: list of field identifiers
        :rtype: list of str
        """
        field_identifiers = []
        for table in self.tables:
            field_identifiers += table.get_field_identifiers()
        for join_item in self.joins:
            field_identifiers += join_item.right_table.get_field_identifiers()
        return field_identifiers

    def build_insert_into(self):
        pass

    def build_withs(self):
        if self.is_inner:
            return ''

        withs = []
        for inner_query in self.with_tables + self.get_inner_queries():
            withs.append(inner_query.get_with_sql())
        if len(withs):
            withs.reverse()
            return 'WITH {0} '.format(', '.join(withs))
        return ''

    def get_inner_queries(self, query=None):
        inner_queries = []
        if query is None:
            query = self
        for table in query.tables:
            if type(table) is QueryTable:
                inner_queries.append(table)
                inner_queries += self.get_inner_queries(table.query)

        return inner_queries

    def build_select_fields(self):
        """
        Generates the sql for the SELECT portion of the query

        :return: the SELECT portion of the query
        :rtype: str
        """
        field_sql = []

        # get the field sql for each table
        for table in self.tables:
            field_sql += table.get_field_sql()

        # get the field sql for each join table
        for join_item in self.joins:
            field_sql += join_item.right_table.get_field_sql()

        # combine all field sql separated by a comma
        sql = 'SELECT {0}{1} '.format(self.get_distinct_sql(), ', '.join(field_sql))
        return sql

    def get_distinct_sql(self):
        if self._distinct and self.distinct_ons:
            raise ValueError('Cannot combine distinct and distinct_on')
        if self._distinct:
            return 'DISTINCT '
        if self.distinct_ons:
            return 'DISTINCT ON ({0}) '.format(', '.join(f.get_sql() for f in self.distinct_ons))
        return ''

    def build_from_table(self):
        """
        Generates the sql for the FROM portion of the query

        :return: the FROM portion of the query
        :rtype: str
        """
        table_parts = []

        # get the table sql for each table
        for table in self.tables:
            sql = table.get_sql()
            if len(sql):
                table_parts.append(sql)

        # combine all table sql separated by a comma
        sql = 'FROM {0} '.format(', '.join(table_parts))

        return sql

    def build_joins(self):
        """
        Generates the sql for the JOIN portion of the query

        :return: the JOIN portion of the query
        :rtype: str
        """
        join_parts = []

        # get the sql for each join object
        for join_item in self.joins:
            join_parts.append(join_item.get_sql())

        # if there are any joins, combine them
        if len(join_parts):
            combined_joins = ' '.join(join_parts)
            return '{0} '.format(combined_joins)
        return ''

    def build_where(self):
        """
        Generates the sql for the WHERE portion of the query

        :return: the WHERE portion of the query
        :rtype: str
        """
        return self._where.get_sql()

    def build_groups(self):
        """
        Generates the sql for the GROUP BY portion of the query

        :return: the GROUP BY portion of the query
        :rtype: str
        """
        # check if there are any groupings
        if len(self.groups):
            groups = []

            # get the group sql for each grouping
            for group in self.groups:
                groups.append(group.get_name())
            return 'GROUP BY {0} '.format(', '.join(groups))
        return ''

    def build_order_by(self, use_alias=True):
        """
        Generates the sql for the ORDER BY portion of the query

        :type use_alias: bool
        :param use_alias: If True, the alias for the field will be used in the order by.
            This is an option before query windows do not use the alias. Defaults to True.

        :return: the ORDER BY portion of the query
        :rtype: str
        """
        # check if there are any sorters
        if len(self.sorters):
            sorters = []

            # get the sql for each sorter
            for sorter in self.sorters:
                sorters.append(sorter.get_name(use_alias=use_alias))
            return 'ORDER BY {0} '.format(', '.join(sorters))
        return ''

    def build_limit(self):
        """
        Generates the sql for the LIMIT and OFFSET portions of the query

        :return: the LIMIT and/or OFFSET portions of the query
        :rtype: str
        """
        if self._limit:
            return self._limit.get_sql()
        return ''

    def find_table(self, table):
        """
        Finds a table by name or alias. The FROM tables and JOIN tables
        are included in the search.

        :type table: str or :class:`ModelBase <django:django.db.models.base.ModelBase>`
        :param table: string of the table name or alias or a ModelBase instance

        :return: The table if it is found, otherwise None
        :rtype: Table or None
        """
        table = TableFactory(table)
        identifier = table.get_identifier()
        join_tables = [join_item.right_table for join_item in self.joins]
        for table in (self.tables + join_tables):
            if table.get_identifier() == identifier:
                return table
        return None

    # TODO: add test for alias
    # TODO: add option to use explicit names or *
    # TODO: add test for optional explicit names
    def wrap(self, alias=None):
        """
        Wraps the query by selecting all fields from itself

        :rtype: :class:`Query <querybuilder.query.Query>`
        :return: The wrapped query
        """
        field_names = self.get_field_names()
        query = Query(self.connection).from_table(deepcopy(self), alias=alias)
        self.__dict__.update(query.__dict__)

        # set explicit field names
        self.tables[0].set_fields(field_names)
        field_names = self.get_field_names()

        return self

    def copy(self):
        """
        Deeply copies everything in the query object except the connection object is shared
        """
        connection = self.connection
        del self.connection
        copied_query = deepcopy(self)
        copied_query.connection = connection
        self.connection = connection
        return copied_query

    def get_args(self):
        """
        Gets the args for the query which will be escaped when being executed by the
        db. All inner queries are inspected and their args are combined with this
        query's args.

        :return: all args for this query as a dict
        :rtype: dict
        """
        for table in self.tables + self.with_tables:
            if type(table) is QueryTable:
                self._where.args.update(table.query.get_args())

        return self._where.args

    def explain(self, sql=None, sql_args=None):
        """
        Runs EXPLAIN on this query

        :type sql: str or None
        :param sql: The sql to run EXPLAIN on. If None is specified, the query will
            use ``self.get_sql()``

        :type sql_args: dict or None
        :param sql_args: A dictionary of the arguments to be escaped in the query. If None and
            sql is None, the query will use ``self.get_args()``

        :rtype: list of str
        :return: list of each line of output from the EXPLAIN statement
        """
        cursor = self.get_cursor()
        if sql is None:
            sql = self.get_sql()
            sql_args = self.get_args()
        elif sql_args is None:
            sql_args = {}

        cursor.execute('EXPLAIN {0}'.format(sql), sql_args)
        rows = self._fetch_all_as_dict(cursor)
        return rows

    def select(self, return_models=False, nest=False, bypass_safe_limit=False, sql=None, sql_args=None):
        """
        Executes the SELECT statement and returns the rows as a list of dictionaries or a list of
        model instances

        :type return_models: bool
        :param return_models: Set to True to return a list of models instead of a list of dictionaries.
            Defaults to False

        :type nest: bool
        :param nest: Set to True to treat all double underscores in keynames as nested data. This will
            convert all keys with double underscores to dictionaries keyed off of the left side of
            the underscores. Ex: {"id": 1", "account__id": 1, "account__name": "Name"} becomes
            {"id": 1, "account": {"id": 1, "name": "Name"}}

        :type bypass_safe_limit: bool
        :param bypass_safe_limit: Ignores the safe_limit option even if the safe_limit is enabled

        :type sql: str or None
        :param sql: The sql to execute in the SELECT statement. If one is not specified, then the
            query will use ``self.get_sql()``

        :type sql_args: str or None
        :param sql_args: The sql args to be used in the SELECT statement. If none are specified, then
            the query wil use ``self.get_args()``

        :rtype: list of dict
        :return: list of dictionaries of the rows
        """
        # Check if we need to set a safe limit
        if bypass_safe_limit is False:
            if Query.enable_safe_limit:
                if self.count() > Query.safe_limit:
                    self.limit(Query.safe_limit)

        # determine which sql to use
        if sql is None:
            sql = self.get_sql()

        # determine which sql args to use
        if sql_args is None:
            sql_args = self.get_args()

        # get the cursor to execute the query
        cursor = self.get_cursor()

        # execute the query
        cursor.execute(sql, sql_args)

        # get the results as a list of dictionaries
        rows = self._fetch_all_as_dict(cursor)

        # check if models should be returned instead of dictionaries
        if return_models:

            # set nesting to true, so the nested models can easily load the data
            nest = True

            # build model map of map name to model
            model_map = {}
            for join_item in self.joins:
                model_map[join_item.right_table.field_prefix] = join_item.right_table.model

        # check if results should be nested
        if nest:

            # convert keys with double underscores to dictionaries
            for row in rows:
                _row = row.copy()
                for key, value in _row.items():
                    set_value_for_keypath(row, key, value, True, '__')
                    if '__' in key:
                        row.pop(key)

            # create models if needed
            if return_models:
                model_class = self.tables[0].model
                new_rows = []
                for row in rows:
                    model = model_class()
                    # assign all non-model keys first because django 1.5 requires
                    # that the model has an id set before setting a property that is
                    # a foreign key
                    for key, value in row.items():
                        if key not in model_map:
                            setattr(model, key, value)
                    # assign all model instances
                    for key, value in row.items():
                        if key in model_map:
                            child_model = model_map[key]()
                            for child_key, child_value in value.items():
                                setattr(child_model, child_key, child_value)
                            value = child_model
                        setattr(model, key, value)
                    new_rows.append(model)
                rows = new_rows

        return rows

    def insert(self, rows):
        """
        Inserts records into the db
        # TODO: implement this
        """
        if len(rows) == 0:
            return

        sql, sql_args = self.get_insert_sql(rows)

        # get the cursor to execute the query
        cursor = self.get_cursor()

        # execute the query
        cursor.execute(sql, sql_args)

    def update(self, rows):
        """
        Updates records in the db
        """
        if len(rows) == 0:
            return

        sql, sql_args = self.get_update_sql(rows)

        # get the cursor to execute the query
        cursor = self.get_cursor()

        # execute the query
        cursor.execute(sql, sql_args)

    def get_auto_field_name(self, model_class):
        """
        If one of the unique_fields is the model's AutoField, return the field name, otherwise return None
        """
        # Get auto field name (a model can only have one AutoField)
        for field in model_class._meta.fields:
            if isinstance(field, AutoField):
                return field.column

        return None

    def upsert(self, rows, unique_fields, update_fields, return_rows=False, return_models=False):
        """
        Performs an upsert with the set of models defined in rows. If the unique field which is meant
        to cause a conflict is an auto increment field, then the field should be excluded when its value is null.
        In this case, an upsert will be performed followed by a bulk_create
        """
        if len(rows) == 0:
            return

        ModelClass = self.tables[0].model

        rows_with_null_auto_field_value = []

        # Get auto field name (a model can only have one AutoField)
        auto_field_name = self.get_auto_field_name(ModelClass)

        # Check if unique fields list contains an auto field
        if auto_field_name in unique_fields:
            # Separate the rows that need to be inserted vs the rows that need to be upserted
            rows_with_null_auto_field_value = [row for row in rows if getattr(row, auto_field_name) is None]
            rows = [row for row in rows if getattr(row, auto_field_name) is not None]

        return_value = []

        if rows:
            sql, sql_args = self.get_upsert_sql(
                rows,
                unique_fields,
                update_fields,
                auto_field_name=auto_field_name,
                return_rows=return_rows or return_models
            )

            # get the cursor to execute the query
            cursor = self.get_cursor()

            # execute the upsert query
            cursor.execute(sql, sql_args)

            if return_rows or return_models:
                return_value.extend(self._fetch_all_as_dict(cursor))

        if rows_with_null_auto_field_value:
            sql, sql_args = self.get_upsert_sql(
                rows_with_null_auto_field_value,
                unique_fields,
                update_fields,
                auto_field_name=auto_field_name,
                only_insert=True,
                return_rows=return_rows or return_models
            )

            # get the cursor to execute the query
            cursor = self.get_cursor()

            # execute the upsert query
            cursor.execute(sql, sql_args)

            if return_rows or return_models:
                return_value.extend(self._fetch_all_as_dict(cursor))

        if return_models:
            ModelClass = self.tables[0].model
            model_objects = [
                ModelClass(**row_dict)
                for row_dict in return_value
            ]

            # Set the state to indicate the object has been loaded from db
            for model_object in model_objects:
                model_object._state.adding = False
                model_object._state.db = 'default'

            return_value = model_objects

        return return_value

    def sql_delete(self):
        """
        Deletes records from the db
        # TODO: implement this
        """
        pass

    def get_count_query(self):
        """
        Copies the query object and alters the field list and order by to do a more efficient count
        """
        query_copy = self.copy()
        if not query_copy.tables:
            raise Exception('No tables specified to do a count')

        for table in query_copy.tables:
            del table.fields[:]

        query_copy.tables[0].add_field(CountField('*'))
        del query_copy.sorters[:]
        return query_copy

    def count(self, field='*'):
        """
        Returns a COUNT of the query by wrapping the query and performing a COUNT
        aggregate of the specified field

        :param field: the field to pass to the COUNT aggregate. Defaults to '*'
        :type field: str

        :return: The number of rows that the query will return
        :rtype: int
        """
        rows = self.get_count_query().select(bypass_safe_limit=True)
        return list(rows[0].values())[0]

    def max(self, field):
        """
        Returns the maximum value of a field in the result set of the query
        by wrapping the query and performing a MAX aggregate of the specified field
        :param field: the field to pass to the MAX aggregate
        :type field: str

        :return: The maximum value of the specified field
        :rtype: int
        """
        q = Query(self.connection).from_table(self, fields=[
            MaxField(field)
        ])
        rows = q.select(bypass_safe_limit=True)
        return list(rows[0].values())[0]

    def min(self, field):
        """
        Returns the minimum value of a field in the result set of the query
        by wrapping the query and performing a MIN aggregate of the specified field
        :param field: the field to pass to the MIN aggregate
        :type field: str

        :return: The minimum value of the specified field
        :rtype: int
        """
        q = Query(self.connection).from_table(self, fields=[
            MinField(field)
        ])
        rows = q.select(bypass_safe_limit=True)
        return list(rows[0].values())[0]

    def sum(self, field):
        """
        Returns the sum of the field in the result set of the query
        by wrapping the query and performing a SUM aggregate of the specified field
        :param field: the field to pass to the SUM aggregate
        :type field: str

        :return: The sum of the specified field
        :rtype: int
        """
        q = Query(self.connection).from_table(self, fields=[
            SumField(field)
        ])
        rows = q.select(bypass_safe_limit=True)
        return list(rows[0].values())[0]

    def avg(self, field):
        """
        Returns the average of the field in the result set of the query
        by wrapping the query and performing an AVG aggregate of the specified field
        :param field: the field to pass to the AVG aggregate
        :type field: str

        :return: The average of the specified field
        :rtype: int
        """
        q = Query(self.connection).from_table(self, fields=[
            AvgField(field)
        ])
        rows = q.select(bypass_safe_limit=True)
        return list(rows[0].values())[0]

    def _fetch_all_as_dict(self, cursor):
        """
        Iterates over the result set and converts each row to a dictionary

        :return: A list of dictionaries where each row is a dictionary
        :rtype: list of dict
        """
        desc = cursor.description
        return [
            dict(zip([col[0] for col in desc], row))
            for row in cursor.fetchall()
        ] class Order(models.Model):
    """
    Order model
    """
    account = models.ForeignKey(Account, on_delete=models.CASCADE)
    revenue = models.FloatField(null=True)
    margin = models.FloatField()
    margin_percent = models.FloatField()
    time = models.DateTimeField() class User(models.Model):
    """
    User model
    """
    email = models.CharField(max_length=256) class QueryTestCase(TestCase):

    def setUp(self):
        super(QueryTestCase, self).setUp()
        user1 = G(User, id=1, email='wes.okes@gmail.com')
        user2 = G(User, id=2, email='two+wes.okes@gmail.com')
        account1 = G(Account, id=1, user=user1, first_name='Wes', last_name='Okes')
        account2 = G(Account, id=2, user=user2, first_name='Wesley', last_name='Okes')

        G(
            Order, account=account1, revenue=200, margin=100, margin_percent=0.5, time=datetime.datetime(2012, 10, 19))
        G(
            Order, account=account1, revenue=100, margin=25, margin_percent=0.25, time=datetime.datetime(2012, 10, 19))
        G(
            Order, account=account2, revenue=500, margin=100, margin_percent=0.2, time=datetime.datetime(2012, 10, 19))
        G(
            Order, account=account2, revenue=1000, margin=600, margin_percent=0.6, time=datetime.datetime(2012, 10, 19)) def get_comparison_str(item1, item2):
    return 'Items are not equal.\nGot:\n{0}\nExpected:\n{1}'.format(item1, item2)
from querybuilder.fields import CountField, AvgField, MaxField, MinField, StdDevField, SumField, VarianceField
from querybuilder.query import Query
from querybuilder.tests.models import Order, User
from querybuilder.tests.query_tests import QueryTestCase, get_comparison_str


class AggregateTest(QueryTestCase):
    def test_count_id(self):
        query = Query().from_table(
            table='test_table',
            fields=[
                CountField('id')
            ]
        )
        query_str = query.get_sql()
        expected_query = 'SELECT COUNT(test_table.id) AS "id_count" FROM test_table'
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_count_distinct(self):
        query = Query().from_table(
            table='test_table',
            fields=[
                CountField('name', distinct=True)
            ]
        )
        query_str = query.get_sql()
        expected_query = 'SELECT COUNT(DISTINCT test_table.name) AS "name_count" FROM test_table'
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_count_all(self):
        query = Query().from_table(
            table='test_table',
            fields=[

                CountField('*')